Finance 221
Time Value of Money Excel Project
Compass Submission Due by 11:59 PM, Sunday, Sept. 19
Please use Excel financial functions or algebraic time value of money equations to answer these questions in your spreadsheet in order to receive any credit. Please type your name at the top of the first page of your workbook.
Part 1: In the future.
Construct a table and a graph showing the relationship between interest rates, time, and future value by showing how $1,000 would grow each successive year over a 30-year period at different interest rates. Use $1,000 for your present value and calculate the future value of this $1,000 each year over the 30-year period at 0%, 2%, 4%, 6%, 8%, 10%, 15%, and 20% compounded annually. Future Value should be the y-axis for your graph and years (or time) should be your x-axis and you should end up with a line for each interest rate on your graph. Please insert your graph (chart) under your table of future values.
Part 2: Today’s Value
Construct a table and a graph showing the relationship between interest rates, time, and present value by showing how $1,000 pushed a year further into the future over a 30-year period would be discounted at different interest rates. Use $1,000 for your future value and calculate the present value of this $1,000 each year over the 30-year period at 0%, 2%, 4%, 6%, 8%, 10%, 15%, and 20% compounded annually. Present Value should be the y-axis for your graph and years (or time) should be your x-axis and you should end up with a line for each interest rate on your graph. Please insert your graph (chart) under your table of present values.
Part 3. Let’s buy a car?
•Prof. Finance decides to buy a 2021 Mazda CX-5 Turbo Signature edition. After paying a down payment and taxes, Prof. Finance can finance the rest of the purchase price with a loan of $24,000 for 60 months at a special finance rate offered by Mazda of 0.9% APR compounded monthly.
•He finds out that Mazda has a second offer of $1500 cash back (rebate) in place of the special 0.9% finance rate offered. Prof. Finance finds he can get 2.49% APR financing online for 60 months if he takes the $1500 cash back offer.
Answer the following questions.
1. What is the effective annual rate for each loan?
2.What would be the monthly car loan payment under the Mazda’s 0.9% APR financing offer (assume a 60-month loan term)?
3.What would be the monthly car loan payment under the Mazda’s $1500 cash back offer and the 2.49% APR pre-approved financing (assume a 60-month loan term)?
4.At what APR would Prof. Finance be indifferent between the two offers? In other words, what APR (assuming a 60-month loan term) for the $1500 cash back offer would have the same monthly payment with the 0.9% APR financing offer?
5.Let’s assume you go with the offer in question #3. Construct an amortization schedule for the loan for all 60 monthly payments (see section 5-18 of the textbook). What is your loan balance after 36 months?
6.The local Mazda dealer has found a special 1.99% APR loan rate for 60 months from Citibank that Prof. Finance qualifies for if he elects the $1500 cash back option. Prof.
Finance says that’s great! What would be the monthly payment under this loan?
7.Prof. Finance is more than happy with the 1.99% APR and $1500 cash back offer but wants a monthly payment of $350 (assume a 60-month loan term) and realizes he will have to put more money down. How much additional money will Prof. Finance have to put down in order to achieve his target monthly payment? Note: original loan amount with cash back was $22,500.
Part 4: Financing Your Retirement
You will graduate in a few years and start working and it’s never too early to start planning for your retirement and other financial events. Let’s fast forward to the beginning of your career. Here’s some assumptions to help you get started.
Your starting annual salary will be $70,000.
You plan to work for 43 years before retiring. You expect your salary to grow at an annual average rate of 4%.
When you retire you want a 24-year retirement annuity that begins 43 years from today with an equal annual payment equal to 80% of your final working year salary. Assume the first retirement annuity payment would occur immediately upon retirement 43 years from today. You realize your purchasing power will decrease over time during retirement.
Assume any retirement fund savings will earn an 8% compounded annually before and after retirement Answer the following questions to help finalize your retirement planning.
1.What is your expected final year working salary?
2.What is your desired annual retirement income?
3.How much will you need at retirement 43 years after the beginning of your career to fund your desired retirement annuity?
4.Imagine you get $30,000 in graduation gifts from your family that you deposit into your retirement savings account at the beginning of your work career. How large does your annual end of the year deposit over your working career (for 43 years) need to be in addition to the initial $30,000 to fund your desired retirement annuity from #3?
5.Looking at your answers in the last two questions, the annual amount, while doable, might be a bit of a financial stretch for you especially early in your career. Let’s assume you will deposit $7500 at the end of each year for 43 years in addition to the initial $30,000. Please answer the following
aHow much will you have in retirement savings at retirement after 43 years?
bHow large of an annual retirement annuity can you withdraw at the beginning of each year for 24 years upon retirement?
Part 5. Mortgage Interest Rates
Mortgage loan commercials and bank banners can be a little confusing due to the fact they report two rates: 1) the stated nominal annual rate that we usually think of as APR which is compounded monthly and is the rate that is used on the actual loan amount to determine the monthly payment, and 2) the “APR” which is somewhat higher and in some ads significantly higher than the first rate. This second “APR” is a stated nominal annual rate but assumes the borrower will finance all closing costs necessary to get the loan (instead of paying them in cash when closing the loan) in addition to the amount the borrower needs to complete the purchase of their home.
Here’s how this second “APR” is determined. First, the first stated annual rate is used to figure the monthly payment on the loan amount plus closing costs. Then, the monthly interest rate that equates the present value of these monthly payments with the loan amount without closing costs is found. Finally, this monthly rate is multiplied by 12 to get this second “APR”. The larger the difference between the two rates the larger the closing costs and fees for the loan. The mortgage ads usually use some fixed loan amount like $100,000, $150,000, or $200,000. Whether the borrower decides to finance the closing costs or not, this second APR can give the borrower a truer cost of borrowing once the closing costs are factored in.
Let’s look at a couple of examples of how this works. Imagine the Prof. Business finds a $375,000 condo she wishes to buy and has a $150,000 down payment and will need to borrow $225,000 before any closing costs. Use this information to answer the questions below.
1.Friendly Mortgage offers a 30-year fixed rate mortgage with a nominal annual rate of 2.75% with $750 in closing costs.
a.What would be Prof. Business’ monthly mortgage payment if she pays the closing costs in cash?
b.What would be Prof. Business’ monthly mortgage payment if she finances the closing costs?
c.What is the second “APR” for this loan as described above?
2.Rapid Loans offers a 30-year fixed rate mortgage with a nominal annual rate of 2.625% with $4500 in closing costs.
a.What would be Prof. Business’ monthly mortgage payment if she pays the closing costs in cash?
b.What would be Prof. Business’ monthly mortgage payment if she finances the closing costs?
c.What is the second “APR” for this loan as described above?
3.Which of the two mortgage options would you recommend to Prof Fin? Explain why.
