# Calculating the time value of money in Excel

Jack has the cash to buy a car but the salesperson argues it would be better for Jack to borrow the funds. The math salesperson gives is that you can earn more on the same $15,100 in an 8% CD than he'd pay out on a 14.2% car loan. By calculating that 48 months of interest on a 14.2% loan of $15,100 would be $4,779.20, while the same principal invested at 8%, compounded monthly would earn interest of $5,672.56 ---- a profit of $893.36. The reasoning, "the 14.2% is applied to a declining balance and the 8% is on an increasing balance."

1) Verify that the numbers for total interest paid ($4,779.20) and total interest earned ($5,672.56), quoted in the article, and are correct (within rounding).

2) Evaluate proposal that Jack borrow rather than cash. Assume there is no risk that you will default on the loan and ignore taxes for now. Construct a spreadsheet that calculates the balance in your money market fund each month assuming that, when you borrow, you keep $15,100 in the fund and withdraw the monthly loan payments from the fund. If this is a "good deal", your balance at month 48 should be positive and represents how much better you are.

3) Evaluate proposal that Jack borrow rather than cash. Use the same approach from part #2 with one change: Assume that the interest earned on your money market fund is taxable at the rate of 35.5% (33% federal plus 2.5% state) while the interest paid on the car loan (personal interest in the parlance of the IRS) is not deductible for tax purposes. For simplicity, assume you pay taxes on interest received each month. By what amount (if any) is your wealth increased or decreased by borrowing?

© BrainMass Inc. brainmass.com October 25, 2018, 10:14 am ad1c9bdddfhttps://brainmass.com/business/the-time-value-of-money/calculating-the-time-value-of-money-in-excel-603483

#### Solution Summary

An Excel file with 3 worksheets with problems regarding the time value of money are provided. Step by step computations and tables are shown for the problems.

Components in Calculating the Time Value of Money

All answers should be in a single Excel file.

Show the step by step calculations how it was done and explained thoroughly by using excel:

1 If you deposit $15,000 today and earn 8% annual interest, how much will you have in 9 years?

Answer: $29,985.07

2 Tiffany will receive a graduation gift of $10,000 from her parents in 3 years. If the discount rate

is 7%, what is this gift worth today?

Answer: $8,162.98

3 What is the present value of a 20-year ordinary annuity of $30,000 using a 6% discount rate?

Answer: $344,097.64

4 You deposit $5,000 in an account that pays 8% interest per annum. How long will it take to double your money?

Answer: 9 years

5 The Johnsons have $60,000 to use as a down-payment on a house, and they want to borrow $240,000

from the bank. The current mortgage interest rate is 5%. If they make equal monthly payments for 30 years,

how much will the monthly payment be?

Answer: $1,288.37

6 Tim paid $250 per month into his 401K retirement plan. After 30 years, he had accumulated $500,000. What

average annual rate of interest had he earned over the 30 years?

Answer: 9.42%

7 Charlotte's firm had sales of $525,000 in the year 2001. By 2012, sales had increased to $1,200,000. What was

the average annual rate of increase?

Answer: 7.80%