Someone is borrowing ~$250,000 from me at 5.5% for 5 years. We have agreed on quarterly payments of $12,500 for the first two years, with the remainder paid off in equal quarterly payments over the following 3 years. I need to create an Excel spreadsheet that calculates the payment amounts—which can be adjusted in the event of prepayments. I would appreciate any help.
There’s not sufficient information in what you say to answer completely. Assuming you mean 5.5% compounded quarterly, there should be a charge of 1.375%. What you would do with prepayment depends on when they come in. Assuming they come in at the end of any quarter it’s relatively simple: I’d set up a spread sheet as follows
first cell labeled blank below is A1. || divide columns
{blank} || {blank} || 250000
=C11.01375 || {12500 or actual payment here} || =A2-B2
=C21.01375 || {12500 or actual payment here} || =A3-B3
you can copy that last row as needed.
Column A will hold the balance due at the end of each quarter before payment starting with A2 holding the balance due at the end of quarter one. Column C will hold the balance due at the end of each quarter after the payment has been made.
It gets a bit more complicated if extra payments are made during a quarter. One method (the simplest) is to just credit any payment made during a quarter as received at quarter end.
If the payment stays at 12500 each quarter, he will owe 6875.21 after 24 quarters so less than a full payment will pay off the loan
The pre-payments would coincide with the quarterly payments.
We used an Excel template for our more “standard” privately funded mortgage. We’ve had it for ~10 years now, so who knows where we sourced ours from.
But I see Microsoft actually provides a useful template for this that looks very similar to the one we used: https://templates.office.com/en-us/Loan-amortization-schedule-TM10073881.
You’re asking for something a little more complicated, but I think you can make it work because the template allows you to set a baseline optional extra payment and also allows you to adjust the optional extra payment per payment. For example, I plugged in your loan details and an “optional extra payment” of negative 1882.63 to get to a payment of 12500.00. You could adjust the individual optional payments for the last three years to get to something closer to what you want. Actually it might be easier to use the ending balance after your two years of 12500.00 payments as the new loan amount on a different schedule for the remaining three years. We’ve done something similar when we “refinanced” with our mortgage holder a few times.
If a prepayment is made, does it adjust the amount of all future payments, the amount of the next payment, or only the final payment?
It adjusts the amount of the final payment (and the amount of compound interest).
I created a quick spreadsheet and came up with a monthly payment of $15,820.48 for the three years of payments after he stops paying $12,500. This assumes no principal prepayments, though I built the capability of taking prepayments into the spreadsheet. I also assumed based on your response that you don’t want to recalculate and reduce the later months’ minimum payment if he prepays a portion of the loan.
First, set up columns in the spreadsheet for:
- Quarterly payment dates
- Beginning balance
- Interest
- Payment
- Additional prepayment
- Ending balance
Then, use as values for each spreadsheet row:
Beginning balance (first row) = $250,000
Beginning balance (every other row) = ending balance from preceding row
Interest (compounded quarterly) = (annual interest rate/4)*principal
payment (for the first eight rows) = $12,500
payment (for the ninth) = -PMT(interest rate/4,12,beginning balance [for that quarter])
payment (for the remaining rows) = payment for the ninth row
Additional payment = whatever extra amount he pays that month. Leave this blank to begin with but enter them as you go.
Ending balance = beginning balance + interest - payment - additional prepayment
The key formula is buried in the middle of the payment row. That formula will calculate the payment for that month based on the beginning balance that month. The underlined number is 12 because it assumes 12 quarterly payments over three years.
If instead you wanted to recalculate the minimum payment each month if he prepays, you could do that too by using the formula in the ninth quarter for the payment in all the rest of the quarters.
Lest us know if you have trouble with the spreadsheet.
I should also note that in the last quarter, he should pay only the beginning balance and the interest.
This spreadsheet won’t work well if he skips a payment. I can email you a spreadsheet that handles that as well but I don’t want to describe it online.
For the first 2 years, the quarterly payment would be $12,500.00. At the end of two years, the principal balance outstanding would be $173,913.82, with $23,913.32 in interest collected during those 2 years, and $76,086.68 applied against the principal. Starting the 3rd year, the quarterly payment would need to be $15,820.48, for the remaining 3 years, to amortize to $0.00.
You mean that a pre-payment waits to the scheduled payment date to be calculated - they pay interest on it ?
In the financial world of today, 5% means 5% PA of compound interest , calculated daily .
So thats 5/365 % PD (per day.) .
There is the option to allow a pre-payment to reduce their interest from the day the pre-payment is made ?
I suspect all he wants to know is how this is done.
In excel, the difference between two dates is the number of days between them. That is, date1 - date2 is the number of days…
So you have each line including the following columns.
DATE , BALANCE BEFORE PAYMENT , AMOUNT OF PAYMENT , BALANCE AFTER PAYMENT
Balance before Payment is the balance FOR that DATE, but before the payment…
If they owe $X at 5% P.A. for 90 days, the new amount owed , after the 90 days, is X * ( ( 1 + 5/365) ^ 90 )
So thats the formula for BALANCE BEFORE PAYMENT.
Of course, you change 90 (90 days) to a formula… such as ( $C$57 - $C$56) the difference of two dates, being the the number of days between the last update, and this payment. If there is no payment and you want an update on amounts owing, interest charged, etc set a payment of zero.
Balance after payment is clearly just subtracting the payment…
If you keep the same sheet up to date, with only actual payments for past dates, and requisite payments for future dates, you have the actual situation both in current amount owing, and projected pay off amounts, dates ,etc.
You have two spreadsheets in one - a simple interest payoff and an amortization
Row 1: Col A: Quarter; Col B: Principal; Col C: Interest; Col D: Payment
Row 2: Col A: 1; Col B: 250000; Col C: =B20.055/4; Col D: 12500
Row 3: Col A: =A2+1; Col B: =B2+C2-D2; Col C: =B30.055/4; Col D: 12500
Now copy/paste Row 3 down to Row 9. That’s your two years of $12,500 payments
Copy A9 & B9 & C9 into row 10 to get the balance for your amortization (equal payments) for 3 years
In D9 you need to type in the amortization formula. I did it as a static reference so you can copy/paste
In D9 type =$B$10*(0.055/4)((1+0.055/4)^(43))/((1+0.055/4)^(4*3)-1)
Now copy Row 9 and paste in Rows 10 through 21
Quod Erat Faciendum
Your payoff table looks like this at the end
Quarter Principal Interest Payment
1 $250,000.00 $3,437.50 $12,500.00
2 $240,937.50 $3,312.89 $12,500.00
3 $231,750.39 $3,186.57 $12,500.00
4 $222,436.96 $3,058.51 $12,500.00
5 $212,995.47 $2,928.69 $12,500.00
6 $203,424.15 $2,797.08 $12,500.00
7 $193,721.24 $2,663.67 $12,500.00
8 $183,884.90 $2,528.42 $12,500.00
9 $173,913.32 $2,391.31 $15,820.48
10 $160,484.15 $2,206.66 $15,820.48
11 $146,870.32 $2,019.47 $15,820.48
12 $133,069.30 $1,829.70 $15,820.48
13 $119,078.52 $1,637.33 $15,820.48
14 $104,895.37 $1,442.31 $15,820.48
15 $90,517.19 $1,244.61 $15,820.48
16 $75,941.32 $1,044.19 $15,820.48
17 $61,165.03 $841.02 $15,820.48
18 $46,185.57 $635.05 $15,820.48
19 $31,000.13 $426.25 $15,820.48
20 $15,605.90 $214.58 $15,820.48