1. Cash Flow Diagrams and Compounded Interest: John decides to take out a loan f

Question

1.      Cash Flow Diagrams and Compounded Interest: John decides to take out a loan for $10,000 for school at an annual interest rate of 10%. He agrees to make four equal payments, but will defer these payments for four years while in school. Starting at the end of the 5th year, he makes four equal annual payments (years 5, 6, 7 and 8). At the end of the 8th year, the loan is paid off.

(a) Create a table that depicts the cash flow diagram for this problem. It should cover years 0 to 8, and should show the balance, interest charges, each payment, how much of that payment went towards interest and how much towards the principal. The balance should be zero at the end of the 8th year. Use trial and error to find the required payment.

(b) Compute the annual payment using one of the built-in Excel functions discussed in class. Show that this gives the same answer as (a).

(c) Compute the present worth and future worth of this investment.

(d) Suppose interest is compounded twice per year instead of annually, but he still makes four annual payments starting at the end of the 5th year. What is the new annual payment?

2.      Kate will purchase a house for $500,000 with a 10% down payment and $10,000 in closing costs. The closing costs are included in the loan. A 10-year loan is arranged at 5% interest compounded monthly.

(a) What is the monthly payment?

(b) By choosing to include the closing costs in the loan, how much money was paid in interest on the closing costs over the life of the loan.

(c) What is the effective interest rate for this loan? (Hint: you must take into account the monthly compounding AND the closing costs.)

(d) Suppose closing costs were paid at closing. How does this change the overall cost of the loan? How does this change the effective interest rate?

(NOTE: EVERYTHING SHOULD BE DONE IN EXCEL. OTHERWISE NO POINTS. INCLUDE THE FUNCTION USED AS WELL)

Explanation / Answer

Here are some useful links to help you understand the functions:

http://www.excel-easy.com/examples/compound-interest.html

http://spreadsheets.about.com/od/excelfunctions/qt/20071020.htm

http://blog.sageintelligence.com/excel-tips-tricks/2013/04/how-use-the-ipmt-function-calculate-the-interest-payment-for-given-period/

Once you go through those, take a look at the excel chart for the first problem here. I've uploaded the excel file so you can use it.
https://www.dropbox.com/s/a68zvvh0bvud368/Albtech%27s%20Finance%20Problem.xlsx

You can click on each cell to find the formula for how it was calculated. Everything is labeled so it's easy to find.

Those websites I've included will tell you how to use and interpret the parameters for each function.

I've left the second problem for you to do as its very similar. It is simply a higher principle with a longer loan period and more frequent compounding, all explained in the websites above.

Also, for question 1 part a where it says "Use trial and error to find the required payment," you can set up a simple one variable equation to solve for the payment without the excel function.

0=(1.1(1.1(1.1(1.1*14641-x)-x)-x)-x)

http://www.wolframalpha.com/input/?i=solve+0%3D%281.1%281.1%281.1%281.1*14641-x%29-x%29-x%29-x%29+for+x

Finally, part C is one of the easier parts of the problem and you can find a detailed explanation of both the present and future worth at this link. I didn't solve it for you in the excel file but after reading the explanation, you should have enough knowledge to easily calculate it.

http://www.investopedia.com/articles/03/101503.asp

Hopefully, you have a better understanding of the problem and how the excel functions work. If you have any questions or need clarification, just comment and I'll reply soon. Good luck with the second problem :)

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.