Once upon a time (all my numbers and loans are fictitious) the loan department was negotiating the sale of a parking lot. It seemed that the buyer and we were at a stalemate. We wanted $5,700,000 for the lot and the buyer was only willing to pay $5,500,000. The market rate for this type of loan was 5.00%. Lending asked me to calculate the interest rate on a $4,700,000, five-year loan, amortized over 360 months, sold to the buyer at his price ($5,500,000). The deal was that we would take his offer if we financed the loan. Lending wanted a rate that would give us back the difference of $200,000 in our price vs. the sales price over the life of the loan.
That was fairly easy to do. I set up an amortization schedule with the interest rate linked to cell C6 (Adjusted Rate). Then I set up a variance cell E9 that added the difference in prices (in this case $200,000) to the loan balance less the present value of the loan payments discounted at the market rate (in this case 5.0%). Then I set up a small macro that would use Excel’s goal seek option and attached a button to run the macro. Each time the button is pushed, goal seek changes the rate in C6, until the variance cell E9 equals zero. The remaining adjusted rate is the break-even rate if the loan runs to the last payment. It is true that I could have solved for the break even rate using goal seek without an amortization schedule, however hold on because I will need the amortization schedule for the next step.
What if the buyer intended to prepay the loan early? Then we would lose the chance to recoup the total $200,000. Now I was asked to calculate a prepayment amount for each month that would guarantee exactly $200,000 in present value.
Present Value refers to 30 days before the first payment is due.
Future Value refers to any point that a payment is due.
The objective is to calculate an amount that must be paid if the borrower wishes to prepay the loan, in addition to the normal payment. That prepayment amount must be sufficient to equate the difference in the actual price paid for the property and the amount we asked for the property (in this case $200,000) as if our offering price was paid originally.
1. The payments received each month (plus principal for the month being solved for) are present valued using the prevailing market rate at the time the loan was originally made (in this case 5.0%).
2. The original loan balance is subtracted from the above present value leaving the gain, so far, by using the higher adjusted rate instead of market rate.
3. The gain from above is subtracted from the difference in the two price ($200,000 in this case)
4. That leaves the remaining present value needed to return the $200,000 in present value terms.
5. Due to the remaining amount being in terms of present value, it must be future valued to the month being calculated, which is the prepayment amount.
To the right of the amortization schedule is a “Calculation Examples” model. If you pick a month, it will explain the math of how the prepayment amount was calculated for that month:
All spreadsheets used Excel 2013