Question: 1. Ms. Manifee would like to purchase a new condo for $105,000. She plans to make a down payment of $50,000 and to borrow the rest of the money from the bank. The bank charges an annual percentage rate of 4% compounded daily. She agrees to monthly payments to pay off the loan in 10 years.
a) Write or copy from an Excel table the amount of interest paid each month, the amount that is paid on the principal each month, and the balance remaining at the end of each month. You only need to copy the Excel table for the first 2 years of the 10-year loan. Please do not attach the Excel file as a separate file in your homework submission.
b) Assume Ms. Manifee has made 14 payments and would like to pay off the balance immediately after the 14th payment. Write down the equation or Excel formula that enables you to calculate this balance. The formula should not be recursive.
2. An individual retirement account (IRA) offers a risk-free option with a nominal 5% interest rate, compounded monthly. You get paid two times per month (the 1st and the 15th of the month) and decide to invest $500 per pay period into the IRA. This is a situation where the payment periods occur more frequently than the compounding periods.
a) First assume that interest is calculated only at the end of the month so that $500 invested at the beginning of the month earns the same amount of interest as $500 invested in the middle of the month. In other words, there is no benefit to invest $500 in the middle of the month versus the end of the month. With this assumption, what is the value of the IRA after 10 years?
b) Now assume that a deposit starts to earn interest whenever the deposit is made, so that there are 0.5 interest periods per payment period. With this assumption, what is the value of the IRA after 10 years?
c) Does the assumption in part a or part b earn more money? Why does this result make intuitive sense?