Financial Literacy Problem Set: Word Document
Skills – Create a Calculator
Create a spreadsheet calculator using the Microsoft Excel™ FV function (Future Value)
Excel™ and other spreadsheets use a built-in function called future value to calculate the future value of an investment or savings account given information about a fixed interest rate and periodic payments.
To be able to use future value to answer multiple problems, you’ll need to set up some cells to collect the input data and a cell to use the function to calculate the output. Open a new spreadsheet and being by setting up cells to collect input data. You’ll need the following values (the Excel™ functions codes are in parentheses by the name of each value.
FV(rate, nper, pmt, [pv], [type]), where rate is the annual % rate,/ periods per year, nper is number of years * periods per year, pmt is (-)payment, [pv] is (optional) present value, and [type] is (optional) whether payment is applied to beginning or end of the period (will will omit).
Annual % rate: the interest rate per period, not in decimal form (for example, if the rate is 5%, enter 5)
Periods per year: The number of payments per year, or the number of compoundings per year.
Number of years: The number of years you’ll make payments. (Use months/12 to enter a partial year)
Payment: The amount you save per payment (enter a positive value). The amount you pay on a loan per payment (enter a negative value). The negative sign used in FV to represent payments to savings deposits has been embedded in the function formula, so this instruction is opposite the one you would find in Excel™ documentation.
Present Value: Any lump sum already existing in the account before regular payments begin, such as a loan balance or a lump sum you would use to open a savings account. Follow the same convention given above for payments vs dividends.
Future Value: Somewhere nearby the above values, place a Future Value cell. This is the cell that will compute the future value of your investment or loan based on your input values.
The following instruction assumes the input values are in the following cells.
Annual % Rate: B3
Periods per year: B4
Number of years: B5
Payment: B6
Present Value: B7
Future Value: into the empty cell where you want the future value computation to appear, type
=FV(B3%/B4, B5*B4, -B6, -B7)
Skills – Use the Calculator
Use the either the spreadsheet calculators your built in the chapter or your new future value calculator to answer each the following problems
- You borrow $1,200 from your brother to buy a new laptop and agree to pay it back with a one-time 10% interest by making monthly payments for one year.
(a) How much is each monthly payment?
(b) How much total interest will you pay?
- Suppose you deposit $750 into a savings account paying 1.8% annual interest and leave it there for 3 years. How much will you have in the account (principal plus interest) at the end of 3 years?
- A Treasury bond matures in 30 years and pay an annual yield of 4% twice a year for the entire term. At the end of the term, you receive the original amount of the bond back. (You cannot use the FV function for this problem without modification, as it assumes annual compounding. Use the appropriate spreadsheet). How much interest will you have received in total if you allow a $10,000 bond to mature?
- You deposit $2,750 into a 5 year CD that pays 1.2% interest, compounded daily. How much money will you have when the CD matures?
- What amount would you need to deposit into a CD paying 1.89% compounded monthly for 3 years to have $5,000 at the end of the term? Use your spreadsheet calculator to answer this question exactly.
- You deposit $150 per month for 10 years into a savings annuity that pays 4.23%. How much will you have in the account after 10 years?
- You’ve saved $300,000 for retirement and are looking at an annuity with a rate of 3%.
(a) If you want to withdraw $1,750 per month, how much money will still be in the account 20 years later?
(b) How much money will you have received in total over the 20 years
- You’d like to purchase a house for $325,000. You’ve found a 30 year mortgage at 3.99% and have $35,000 to put down. Use your loans calculator to answer this question.
(a) How much will you need to borrow?
(b) How much will your monthly payment be?
(c) How much will you have paid the bank by the end of the 30 years?
(d) If you increase your monthly payment by $500, how many years will it take to pay off the loan?
- You have a credit card with a $9,000 balance on it and would like to pay it off in 3 years. The interest rate is 11.5%. How much will you need to pay each month if you don’t accrue any new charges?
- You’ve looked at your budget, and you can afford $425 per month for a car payment. You have a trade-in worth $7,000. How expensive a car can you purchase if the loan terms were 5% interest for 4 years?
Concepts and Explorations
- A CD-ladder is a way to be able to earn higher interest on longer term CDs while still keeping some of your money more accessible in the short run. To build a CD ladder, take some amount of money you’d like to invest and spread it out over several CDs with different maturity terms. Then, as the CDs mature, take the money and reinvest it in the highest interest CD.
Say you receive a lump sum of $20,000 and would like to invest it but don’t know if you’ll need access to any of it in a few years. So, you decide to build a CD ladder by investing 5 equal parts of the sum into 5 separate CDs, maturing in 1, 2, 3, 4, and 5 years, respectively. Assume all CDs compound daily.
(a) If the CDs have the interest rates shown below, how much money would you have in 10 years, assuming that when you get to the 5 year CD, you reinvest it in for another 5 years?
1-year CD rate 0.9%;
2-year CD rate 1.02%;
3-year CD rate 1.4%;
4-year CD rate 1.6 %.
5-year CD rate 1.89%
(b) If you had invested the entire $20,000 into a 10 year CD paying 1.89%, how much more money would you have had?
(c) Look online or in your local newspaper to find banks advertising CD rates. Make a CD ladder of your own with an imaginary windfall. It doesn’t have to be $20k. Imagine you received a large commission on a sale or a hiring bonus. Is there a different savings or investment product that you can find that looks interesting? Speculate and compare rates to see what might earn the bigger return.
- You take out a 30 year mortgage at 5% interest for $150,000 to purchase a house.
(a) How much are your monthly payments?
(b) How much will you still have to pay on the loan after paying on it for 20 years?
(c) Find the first month’s payment in which your payment applies more money to your principal than to the interest. (Hint: use an amortization schedule).
- You purchased two pairs of jeans, new basketball shoes, and a hoodie at your favorite store last month. It was a good deal. You applied for the store credit card and received 30% off when you were approved. The total charge came to $263.41. That’s $355 for the sale, less 30% plus tax. The card comes with an APR of 23%.
(a) How much will you need to pay each month if you want to pay the card off in 10 months?
(b) If you take 10 months to pay the card off, will the interest you pay overcome the 30% discount on the purchase? How much will you have paid in interest?
(c) If you took 10 months to pay off the card, how much of a discount did you end up with? (Subtract the interest paid from the original discount, then find the percentage)
(d) If you only made the minimum payment of $10 per month, how long would it take to pay off the card?
(e) How much in interest would you have paid making only the minimum monthly payment of $10?
(f) Discuss the pros and cons of using a high-interest store card to make purchases. Look up the terms for the cards for stores that you shop and compare. Which of your favorite stores offers the best deal? Include considerations such as discounts and privileges that come with these cards as well as how it could help or hurt someone trying to build a strong credit history.
