Share
Explore BrainMass

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?

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.

$2.19