TVM 18 - Debt Retirement Fund
Using Excel to Determine a Payment Amount
Richard borrowed $92,000 on April 1st, 2020. This amount plus accrued interest at 10.6%, compounded semi-annually, is to be repaid April 1st, 2030. Richard plans to contribute to a debt retirement fund five equal amounts starting on April 1st, 2025, and for the next four years. The fund is expected to earn 9.5% per year. How much must Richard contribute each year to provide a fund to retire the debt on April 1st, 2030?
The first part of the problem is a future value of a single sum problem. We enter the data. PV is 92,000. The rate is 5.3%, or one half of 10.6%. The number of periods, NPER, is 20, or two times the number of years. So we look for the future value function. We look for FV. We click on PV. We click on Rate. And we click on NPER. We hit OK. The future value is $258,437.
In the second part of the problem, we’re looking for the payment, PMT. We know the rate is 9.5%. Number of periods, NPER, is 5. The future value we just computed at -258,437, and the type is 1, since we’re making the deposit at the beginning of the year. Using the PMT function, we click on Rate. We click on NPER. We click on FV. Click on Type. And we find the payment is $39,046.