Amortisation Schedule with payment at the beginning of the period and balloon

Ironadhammm

New Member
Joined
Mar 24, 2022
Messages
4
Office Version
  1. 365
Hi all,

Im struggling to replicate the amortisation schedule sent to me by the bank.
  • The loan principal is 510,733.02
  • The interest rate is supposed to be 3.81%
  • The duration is for 5 years
  • Repayments are made at the beginning of each period
  • There is a $199,999.97 balloon at the end
I've used the PMT function to obtain the repayment amount (which i'm able to correctly calculate), however, i'm not getting the correct interest rate (the rate is supposed to be 3.81%, i'm getting 4.05%) or the interest amount paid per period.

I'm using the RATE and IPMT formulas, inputting the Balloon as the future value, but i'm unable to get my workings to match the banks schedule.

I think it has something to do with the number of periods and the balloon but am unsure.

My workings are in column G of the spreadsheet below.

Can someone please help?

Screen Shot 2022-03-25 at 11.48.28 am.png


Regards
Adam
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Welcome to Mr Excel,

Posting with an image and this small type is impossible for someone to see and review.
It's best to show your example using XL2BB
 
Upvote 0
Hi Thank you. Here is the xl2bb extract below:

Amortisation Schedule.xlsx
ABCDEFGHIJKL
1Bank Amortisation ScheduleMy Workings
2
3Repayment NoDateInterestRepayment AmountBalanceInterestRate4.05%Formula:
4510,733.02Repayment$6,383.60Formula:
5110/2020-6,383.60504,349.42$0.00
6211/20201,580.646,383.60499,546.46$1,702.89
7312/20201,565.586,383.60494,728.44$1,687.08
8401/20211,550.486,383.60489,895.32$1,671.23
9502/20211,535.346,383.60485,047.06$1,655.32
10603/20211,520.146,383.60480,183.60$1,639.35
11704/20211,504.906,383.60475,304.90$1,623.33
12805/20211,489.616,383.60470,410.91$1,607.26
13906/20211,474.276,383.60465,501.58$1,591.13
141007/20211,458.896,383.60460,576.87$1,574.95
151108/20211,443.456,383.60455,636.72$1,558.72
161209/20211,427.976,383.60450,681.09$1,542.43
171310/20211,412.446,383.60445,709.93$1,526.08
181411/20211,396.866,383.60440,723.19$1,509.68
191512/20211,381.236,383.60435,720.82$1,493.22
201601/20221,365.556,383.60430,702.77$1,476.71
211702/20221,349.836,383.60425,669.00$1,460.14
221803/20221,334.056,383.60420,619.45$1,443.52
231904/20221,318.236,383.60415,554.08$1,426.84
242005/20221,302.356,383.60410,472.83$1,410.10
252106/20221,286.436,383.60405,375.66$1,393.31
262207/20221,270.456,383.60400,262.51$1,376.46
272308/20221,254.436,383.60395,133.34$1,359.56
282409/20221,238.356,383.60389,988.09$1,342.59
292510/20221,222.236,383.60384,826.72$1,325.57
302611/20221,206.056,383.60379,649.17$1,308.49
312712/20221,189.826,383.60374,455.39$1,291.36
322801/20231,173.556,383.60369,245.34$1,274.16
332902/20231,157.226,383.60364,018.96$1,256.91
343003/20231,140.846,383.60358,776.20$1,239.60
353104/20231,124.416,383.60353,517.01$1,222.24
363205/20231,107.936,383.60348,241.34$1,204.81
373306/20231,091.396,383.60342,949.13$1,187.32
383407/20231,074.816,383.60337,640.34$1,169.78
393508/20231,058.176,383.60332,314.91$1,152.17
403609/20231,041.486,383.60326,972.79$1,134.51
413710/20231,024.746,383.60321,613.93$1,116.79
423811/20231,007.946,383.60316,238.27$1,099.00
433912/2023991.096,383.60310,845.76$1,081.16
444001/2024974.196,383.60305,436.35$1,063.26
454102/2024957.246,383.60300,009.99$1,045.29
464203/2024940.236,383.60294,566.62$1,027.27
474304/2024923.176,383.60289,106.19$1,009.19
484405/2024906.066,383.60283,628.65$991.04
494506/2024888.906,383.60278,133.95$972.83
504607/2024871.676,383.60272,622.02$954.56
514708/2024854.406,383.60267,092.82$936.23
524809/2024837.076,383.60261,546.29$917.84
534910/2024819.696,383.60255,982.38$899.38
545011/2024802.256,383.60250,401.03$880.87
555112/2024784.766,383.60244,802.19$862.29
565201/2025767.216,383.60239,185.80$843.65
575302/2025749.616,383.60233,551.81$824.94
585403/2025731.956,383.60227,900.16$806.17
595504/2025714.246,383.60222,230.80$787.34
605605/2025696.476,383.60216,543.67$768.45
615706/2025678.656,383.60210,838.72$749.49
625807/2025660.776,383.60205,115.89$730.46
635908/2025642.846,383.60199,375.13$711.38
646009/2025624.84-199,999.97$692.22
65Total65,899.35376,632.40$71,609.94
Sheet1
Cell Formulas
RangeFormula
J3J3=RATE(A64,D5,-E4,E64,1)*12
J4J4=PMT(J3/12,A64,-E4,E64,1)
G5:G64G5=IPMT($J$3/12,A5,$A$64,-510733.02,199999.97,1)
G65G65=SUM(G5:G64)
 
Upvote 0
I calculated a totally different monthly payment:

Book2
ABCDE
1PV$510,733.02Rate:
2Int/Yr3.81%3.81%4.05%
3Years5
4Mo'ly ($12,354.15)($12,388.46)
5Balloon$199,999.97
Sheet2
Cell Formulas
RangeFormula
D2D2=RATE(12*B3,B4,B1,B5,1)*12
B4B4=PMT(B2/12,12*B3,B1,B5,1)
E4E4=PMT(E2/12,12*B3,B1,B5,1)
 
Upvote 0
This is whats confusing me. That amortisation schedule sent from the bank give me a repayment amount of 6,383.60. Working backwards from that I get 4.05%.

I also note that I had PV negative where as you had yours as positive.

Regards
 
Upvote 0
Loan Amortization.xlsm
ABCDE
1Bank Amortisation ScheduleRate3.81%
2Payment$6,331.02
3#DatePaymentInterestBalance
4510,733.02
51Oct-206,331.021,621.58506,023.58
62Nov-206,331.021,606.62499,546.46
73Dec-206,331.021,586.06494,728.44
84Jan-216,331.021,570.76489,895.32
95Feb-216,331.021,555.42485,047.06
106Mar-216,331.021,540.02480,183.60
117Apr-216,331.021,524.58475,304.90
128May-216,331.021,509.09470,410.91
139Jun-216,331.021,493.55465,501.58
1410Jul-216,331.021,477.97460,576.87
1511Aug-216,331.021,462.33455,636.72
1612Sep-216,331.021,446.65450,681.09
1713Oct-216,331.021,430.91445,709.93
1814Nov-216,331.021,415.13440,723.19
1915Dec-216,331.021,399.30435,720.82
2016Jan-226,331.021,383.41430,702.77
2117Feb-226,331.021,367.48425,669.00
2218Mar-226,331.021,351.50420,619.45
2319Apr-226,331.021,335.47415,554.08
2420May-226,331.021,319.38410,472.83
2521Jun-226,331.021,303.25405,375.66
2622Jul-226,331.021,287.07400,262.51
2723Aug-226,331.021,270.83395,133.34
2824Sep-226,331.021,254.55389,988.09
2925Oct-226,331.021,238.21384,826.72
3026Nov-226,331.021,221.82379,649.17
3127Dec-226,331.021,205.39374,455.39
3228Jan-236,331.021,188.90369,245.34
3329Feb-236,331.021,172.35364,018.96
3430Mar-236,331.021,155.76358,776.20
3531Apr-236,331.021,139.11353,517.01
3632May-236,331.021,122.42348,241.34
3733Jun-236,331.021,105.67342,949.13
3834Jul-236,331.021,088.86337,640.34
3935Aug-236,331.021,072.01332,314.91
4036Sep-236,331.021,055.10326,972.79
4137Oct-236,331.021,038.14321,613.93
4238Nov-236,331.021,021.12316,238.27
4339Dec-236,331.021,004.06310,845.76
4440Jan-246,331.02986.94305,436.35
4541Feb-246,331.02969.76300,009.99
4642Mar-246,331.02952.53294,566.62
4743Apr-246,331.02935.25289,106.19
4844May-246,331.02917.91283,628.65
4945Jun-246,331.02900.52278,133.95
5046Jul-246,331.02883.08272,622.02
5147Aug-246,331.02865.57267,092.82
5248Sep-246,331.02848.02261,546.29
5349Oct-246,331.02830.41255,982.38
5450Nov-246,331.02812.74250,401.03
5551Dec-246,331.02795.02244,802.19
5652Jan-256,331.02777.25239,185.80
5753Feb-256,331.02759.41233,551.81
5854Mar-256,331.02741.53227,900.16
5955Apr-256,331.02723.58222,230.80
6056May-256,331.02705.58216,543.67
6157Jun-256,331.02687.53210,838.72
6258Jul-256,331.02669.41205,115.89
6359Aug-256,331.02651.24199,375.13
6460Sep-256,331.02633.02199,999.97
65Total6,331.02199,999.97
3b
Cell Formulas
RangeFormula
E2E2=PMT(E1/12,A64,-E4,E64,0)
E5E5=E4-C5+D5
D5:D64D5=E4*$E$1/12
C5:C65C5=$E$2
 
Upvote 0
Review formula. I changed type to 0
Included first payment
Included interest on last payment


Loan Amortization.xlsm
ABCDE
1Bank Amortisation ScheduleRate3.81%
2Payment$6,331.02
3#DatePaymentInterestBalance
4510,733.02
51Oct-206,331.021,621.58506,023.58
62Nov-206,331.021,606.62499,546.46
73Dec-206,331.021,586.06494,728.44
84Jan-216,331.021,570.76489,895.32
95Feb-216,331.021,555.42485,047.06
106Mar-216,331.021,540.02480,183.60
117Apr-216,331.021,524.58475,304.90
128May-216,331.021,509.09470,410.91
139Jun-216,331.021,493.55465,501.58
1410Jul-216,331.021,477.97460,576.87
1511Aug-216,331.021,462.33455,636.72
1612Sep-216,331.021,446.65450,681.09
1713Oct-216,331.021,430.91445,709.93
1814Nov-216,331.021,415.13440,723.19
1915Dec-216,331.021,399.30435,720.82
2016Jan-226,331.021,383.41430,702.77
2117Feb-226,331.021,367.48425,669.00
2218Mar-226,331.021,351.50420,619.45
2319Apr-226,331.021,335.47415,554.08
2420May-226,331.021,319.38410,472.83
2521Jun-226,331.021,303.25405,375.66
2622Jul-226,331.021,287.07400,262.51
2723Aug-226,331.021,270.83395,133.34
2824Sep-226,331.021,254.55389,988.09
2925Oct-226,331.021,238.21384,826.72
3026Nov-226,331.021,221.82379,649.17
3127Dec-226,331.021,205.39374,455.39
3228Jan-236,331.021,188.90369,245.34
3329Feb-236,331.021,172.35364,018.96
3430Mar-236,331.021,155.76358,776.20
3531Apr-236,331.021,139.11353,517.01
3632May-236,331.021,122.42348,241.34
3733Jun-236,331.021,105.67342,949.13
3834Jul-236,331.021,088.86337,640.34
3935Aug-236,331.021,072.01332,314.91
4036Sep-236,331.021,055.10326,972.79
4137Oct-236,331.021,038.14321,613.93
4238Nov-236,331.021,021.12316,238.27
4339Dec-236,331.021,004.06310,845.76
4440Jan-246,331.02986.94305,436.35
4541Feb-246,331.02969.76300,009.99
4642Mar-246,331.02952.53294,566.62
4743Apr-246,331.02935.25289,106.19
4844May-246,331.02917.91283,628.65
4945Jun-246,331.02900.52278,133.95
5046Jul-246,331.02883.08272,622.02
5147Aug-246,331.02865.57267,092.82
5248Sep-246,331.02848.02261,546.29
5349Oct-246,331.02830.41255,982.38
5450Nov-246,331.02812.74250,401.03
5551Dec-246,331.02795.02244,802.19
5652Jan-256,331.02777.25239,185.80
5753Feb-256,331.02759.41233,551.81
5854Mar-256,331.02741.53227,900.16
5955Apr-256,331.02723.58222,230.80
6056May-256,331.02705.58216,543.67
6157Jun-256,331.02687.53210,838.72
6258Jul-256,331.02669.41205,115.89
6359Aug-256,331.02651.24199,375.13
6460Sep-256,331.02633.02199,999.97
65Total6,331.02199,999.97
3b
Cell Formulas
RangeFormula
E2E2=PMT(E1/12,A64,-E4,E64,0)
E5E5=E4-C5+D5
D5:D64D5=E4*$E$1/12
C5:C65C5=$E$2
 
Upvote 0
Hi Dave,

Thanks for that. Still a variance between the repayments by about 52.58 per repayment. Also the interest in column D doesnt match the banks schedule in my spreadsheet as show in column C.
 
Upvote 0
The amortization schedule that I showed shows a 0 balance.

How are mortgages calculated in your country?
N.B. In Canada and possibly other counties the formula is different.

Please provide complete information on the type of calculation, rates, payments etc.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top