Question

1) Epitome Healthcare has just borrowed $1,000,000 on a five-year, annual payment term loan at a 15% rate. The first payment is due one year from now. Construct the amortization schedule for this loan.

2) John Adams is the CEO of a nursing home in San Jose. He is now 50 years old and plans to retire in ten years. He expects to live for 25 years after he retires – that is, until he is 85. He wants a fixed retirement income that has the same purchasing power at the time he retires as $95,000 has today. He realizes that the real value of his retirement income will decline year by year after he retires. Inflation is expected to be 3 percent per year for ten years. Ignore the inflation rate after John retires – that is, only use the inflation rate to calculate desired retirement income. His retirement income will begin on the day he retires, ten years from today, and he will then get 24 additional annual payments (Hint: this is an annuity due). He currently has $1 million in his IRA, and he expects to earn an annual rate of return on his investments of 5 percent. To the nearest dollar, how much must he contribute to his IRA during each of the next ten years (with deposits being made at the end of each year) to meet his retirement goal?

Answer 1

1. Using the PMT function in excel, the annual payment is found first.

The input for PMT function is

rate = 15%

nper = 5

PV = 1,000,000 (loan amount)

Every year, from the payment, the interest component will be the interest on the outstanding balance and remaining will be paid towards the principal. The amortization schedule is as shown below.