Monthly Loan Payment Using Compound Interest

koolwaters

Active Member
Joined
May 16, 2007
Messages
403
I need to create a loan amortization schedule using Access. My challenge comes as we are using compound interest and I need to determine the monthly payments.

I am able to get the total to be repaid using the formula:
=[LoanAmount]*((1+[InterestRate]/[CompoundType)^([LoanTerm]*CompoundType))

For example:
Loan Amount $15,000
Interest Rate 5%
Compound Type Monthly
Loan Term 5 Years

My formula returns $19,250.38.

What I need to do is determine what the monthly payment should be for that same loan using compound interest but I cannot seem to figure it out. If I use the PMT function in VBA, that gives me $283.07 as the monthly payment, which equates to $16,984.11 over the 5 years, a difference of $2,266.37.

Thanks for any feedback.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Your $283.07 seems to be right. Each month you are paying down on the principal, so you are not paying interest on the whole 15,000 for 60 months.
See table below.
Excel Workbook
ABCDE
1Loan Data
2Original Principal$ 15,000
3Loan Term (Years)5
4Annual Interest Rate5.00%
5Payments per Year12
6Payment$283.07
7
8MonthPaymentInterestPrincipalBalance
9015,000.00
101283.0762.50220.5714,779.43
112283.0761.58221.4914,557.94
123283.0760.66222.4114,335.53
134283.0759.73223.3414,112.20
145283.0758.80224.2713,887.93
156283.0757.87225.2013,662.73
167283.0756.93226.1413,436.59
178283.0755.99227.0813,209.50
189283.0755.04228.0312,981.47
1910283.0754.09228.9812,752.50
2011283.0753.14229.9312,522.56
2112283.0752.18230.8912,291.67
Sheet
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A101. / Formula is =$A10>($B$3*$B$5)Abc
A102. / Formula is =$A10=($B$3*$B$5)Abc
B101. / Formula is =$A10>($B$3*$B$5)Abc
B102. / Formula is =$A10=($B$3*$B$5)Abc
C101. / Formula is =$A10>($B$3*$B$5)Abc
C102. / Formula is =$A10=($B$3*$B$5)Abc
D101. / Formula is =$A10>($B$3*$B$5)Abc
D102. / Formula is =$A10=($B$3*$B$5)Abc
E101. / Formula is =$A10>($B$3*$B$5)Abc
E102. / Formula is =$A10=($B$3*$B$5)Abc
A111. / Formula is =$A10>($B$3*$B$5)Abc
A112. / Formula is =$A10=($B$3*$B$5)Abc
B111. / Formula is =$A10>($B$3*$B$5)Abc
B112. / Formula is =$A10=($B$3*$B$5)Abc
C111. / Formula is =$A10>($B$3*$B$5)Abc
C112. / Formula is =$A10=($B$3*$B$5)Abc
D111. / Formula is =$A10>($B$3*$B$5)Abc
D112. / Formula is =$A10=($B$3*$B$5)Abc
E111. / Formula is =$A10>($B$3*$B$5)Abc
E112. / Formula is =$A10=($B$3*$B$5)Abc
A121. / Formula is =$A10>($B$3*$B$5)Abc
A122. / Formula is =$A10=($B$3*$B$5)Abc
B121. / Formula is =$A10>($B$3*$B$5)Abc
B122. / Formula is =$A10=($B$3*$B$5)Abc
C121. / Formula is =$A10>($B$3*$B$5)Abc
C122. / Formula is =$A10=($B$3*$B$5)Abc
D121. / Formula is =$A10>($B$3*$B$5)Abc
D122. / Formula is =$A10=($B$3*$B$5)Abc
E121. / Formula is =$A10>($B$3*$B$5)Abc
E122. / Formula is =$A10=($B$3*$B$5)Abc
A131. / Formula is =$A10>($B$3*$B$5)Abc
A132. / Formula is =$A10=($B$3*$B$5)Abc
B131. / Formula is =$A10>($B$3*$B$5)Abc
B132. / Formula is =$A10=($B$3*$B$5)Abc
C131. / Formula is =$A10>($B$3*$B$5)Abc
C132. / Formula is =$A10=($B$3*$B$5)Abc
D131. / Formula is =$A10>($B$3*$B$5)Abc
D132. / Formula is =$A10=($B$3*$B$5)Abc
E131. / Formula is =$A10>($B$3*$B$5)Abc
E132. / Formula is =$A10=($B$3*$B$5)Abc
A141. / Formula is =$A10>($B$3*$B$5)Abc
A142. / Formula is =$A10=($B$3*$B$5)Abc
B141. / Formula is =$A10>($B$3*$B$5)Abc
B142. / Formula is =$A10=($B$3*$B$5)Abc
C141. / Formula is =$A10>($B$3*$B$5)Abc
C142. / Formula is =$A10=($B$3*$B$5)Abc
D141. / Formula is =$A10>($B$3*$B$5)Abc
D142. / Formula is =$A10=($B$3*$B$5)Abc
E141. / Formula is =$A10>($B$3*$B$5)Abc
E142. / Formula is =$A10=($B$3*$B$5)Abc
A151. / Formula is =$A10>($B$3*$B$5)Abc
A152. / Formula is =$A10=($B$3*$B$5)Abc
B151. / Formula is =$A10>($B$3*$B$5)Abc
B152. / Formula is =$A10=($B$3*$B$5)Abc
C151. / Formula is =$A10>($B$3*$B$5)Abc
C152. / Formula is =$A10=($B$3*$B$5)Abc
D151. / Formula is =$A10>($B$3*$B$5)Abc
D152. / Formula is =$A10=($B$3*$B$5)Abc
E151. / Formula is =$A10>($B$3*$B$5)Abc
E152. / Formula is =$A10=($B$3*$B$5)Abc
A161. / Formula is =$A10>($B$3*$B$5)Abc
A162. / Formula is =$A10=($B$3*$B$5)Abc
B161. / Formula is =$A10>($B$3*$B$5)Abc
B162. / Formula is =$A10=($B$3*$B$5)Abc
C161. / Formula is =$A10>($B$3*$B$5)Abc
C162. / Formula is =$A10=($B$3*$B$5)Abc
D161. / Formula is =$A10>($B$3*$B$5)Abc
D162. / Formula is =$A10=($B$3*$B$5)Abc
E161. / Formula is =$A10>($B$3*$B$5)Abc
E162. / Formula is =$A10=($B$3*$B$5)Abc
A171. / Formula is =$A10>($B$3*$B$5)Abc
A172. / Formula is =$A10=($B$3*$B$5)Abc
B171. / Formula is =$A10>($B$3*$B$5)Abc
B172. / Formula is =$A10=($B$3*$B$5)Abc
C171. / Formula is =$A10>($B$3*$B$5)Abc
C172. / Formula is =$A10=($B$3*$B$5)Abc
D171. / Formula is =$A10>($B$3*$B$5)Abc
D172. / Formula is =$A10=($B$3*$B$5)Abc
E171. / Formula is =$A10>($B$3*$B$5)Abc
E172. / Formula is =$A10=($B$3*$B$5)Abc
A181. / Formula is =$A10>($B$3*$B$5)Abc
A182. / Formula is =$A10=($B$3*$B$5)Abc
B181. / Formula is =$A10>($B$3*$B$5)Abc
B182. / Formula is =$A10=($B$3*$B$5)Abc
C181. / Formula is =$A10>($B$3*$B$5)Abc
C182. / Formula is =$A10=($B$3*$B$5)Abc
D181. / Formula is =$A10>($B$3*$B$5)Abc
D182. / Formula is =$A10=($B$3*$B$5)Abc
E181. / Formula is =$A10>($B$3*$B$5)Abc
E182. / Formula is =$A10=($B$3*$B$5)Abc
A191. / Formula is =$A10>($B$3*$B$5)Abc
A192. / Formula is =$A10=($B$3*$B$5)Abc
B191. / Formula is =$A10>($B$3*$B$5)Abc
B192. / Formula is =$A10=($B$3*$B$5)Abc
C191. / Formula is =$A10>($B$3*$B$5)Abc
C192. / Formula is =$A10=($B$3*$B$5)Abc
D191. / Formula is =$A10>($B$3*$B$5)Abc
D192. / Formula is =$A10=($B$3*$B$5)Abc
E191. / Formula is =$A10>($B$3*$B$5)Abc
E192. / Formula is =$A10=($B$3*$B$5)Abc
A201. / Formula is =$A10>($B$3*$B$5)Abc
A202. / Formula is =$A10=($B$3*$B$5)Abc
B201. / Formula is =$A10>($B$3*$B$5)Abc
B202. / Formula is =$A10=($B$3*$B$5)Abc
C201. / Formula is =$A10>($B$3*$B$5)Abc
C202. / Formula is =$A10=($B$3*$B$5)Abc
D201. / Formula is =$A10>($B$3*$B$5)Abc
D202. / Formula is =$A10=($B$3*$B$5)Abc
E201. / Formula is =$A10>($B$3*$B$5)Abc
E202. / Formula is =$A10=($B$3*$B$5)Abc
A211. / Formula is =$A10>($B$3*$B$5)Abc
A212. / Formula is =$A10=($B$3*$B$5)Abc
B211. / Formula is =$A10>($B$3*$B$5)Abc
B212. / Formula is =$A10=($B$3*$B$5)Abc
C211. / Formula is =$A10>($B$3*$B$5)Abc
C212. / Formula is =$A10=($B$3*$B$5)Abc
D211. / Formula is =$A10>($B$3*$B$5)Abc
D212. / Formula is =$A10=($B$3*$B$5)Abc
E211. / Formula is =$A10>($B$3*$B$5)Abc
E212. / Formula is =$A10=($B$3*$B$5)Abc
 
Upvote 0
The PMT function is not what I want as that uses the reducing balance method, where the interest is charged on the remaining loan balance.

With the compound interest method, the interest is calculated on the present value of the loan as well as on the accumulated interest.
 
Upvote 0
$283.07 with compound interest will give you $19,250.38.

Formula to calculate the payment would be:
Payment=(Future Value*rate)/[(1+rate)^periods]-1
Excel Workbook
ABCD
1Monthly
2FV =19,250.38
3rate (annually)5.00%0.004167
4Years560
5
6Payment / month283.07
7
8Check using FV function$19,250.38
9
10MonthPaymentInterestBalance
11015,000.00
121283.0762.5015,062.50
132283.0762.7615,125.26
143283.0763.0215,188.28
154283.0763.2815,251.57
165283.0763.5515,315.12
176283.0763.8115,378.93
187283.0764.0815,443.01
198283.0764.3515,507.35
209283.0764.6115,571.97
2110283.0764.8815,636.85
6958283.0779.2219,090.96
7059283.0779.5519,170.50
7160283.0779.8819,250.38
Sheet
 
Upvote 0
I replicated what you have done in Excel for testing purposes.

I added a column for the principal, which is the difference between the interest and the monthly payment.

My interest figures and balance matches yours. However, if you were to add all of the interest and principal payments it only equals $16,984.20. Additionally, if you multiply the monthly payment of $283.07 by 60, you also get $16,984.20. This is a difference of $2,266.18 and therefore means that the person will not have repaid the full $19,250.38 so the monthly payment does not seem to be correct.


Book1
ABCDEF
1Monthly
2FV=$19,250.38
3Rate (annually)5%0.004166667
4Years560
5
6Payment per Mth$283.07
7
8Check Using FV Function$19,250.38
9
10Payment #Beginning BalanceAmoutn DueInterestPrincipalEnding Balance
11115,000.00283.0762.50220.5715,062.50
12215,062.50283.0762.76220.3115,125.26
13315,125.26283.0763.02220.0515,188.28
14415,188.28283.0763.28219.7915,251.57
15515,251.57283.0763.55219.5215,315.12
16615,315.12283.0763.81219.2615,378.93
17715,378.93283.0764.08218.9915,443.01
18815,443.01283.0764.35218.7215,507.35
19915,507.35283.0764.61218.4615,571.97
201015,571.97283.0764.88218.1915,636.85
685819,011.74283.0779.22203.8519,090.96
695919,090.96283.0779.55203.5219,170.50
706019,170.50283.0779.88203.1919,250.38
714,250.3812,733.82
72Total Interest & Principal16,984.20
73Difference(2,266.18)
Sheet1
Cell Formulas
RangeFormula
C3=B3/12
C4=B4*12
B6=(B2*C3)/((1+C3)^C4-1)
B8=FV(B3/12,B4*12,-B6)
B12=F11
B13=F12
B14=F13
B15=F14
B16=F15
B17=F16
B18=F17
B19=F18
B20=F19
B68=F67
B69=F68
B70=F69
D11=B11*($B$3/12)
D12=F11*($B$3/12)
D13=F12*($B$3/12)
D14=F13*($B$3/12)
D15=F14*($B$3/12)
D16=F15*($B$3/12)
D17=F16*($B$3/12)
D18=F17*($B$3/12)
D19=F18*($B$3/12)
D20=F19*($B$3/12)
D68=F67*($B$3/12)
D69=F68*($B$3/12)
D70=F69*($B$3/12)
D71=SUM(D11:D70)
D72=SUM(D71:E71)
D73=D72-B8
E11=C11-D11
E12=C12-D12
E13=C13-D13
E14=C14-D14
E15=C15-D15
E16=C16-D16
E17=C17-D17
E18=C18-D18
E19=C19-D19
E20=C20-D20
E68=C68-D68
E69=C69-D69
E70=C70-D70
E71=SUM(E11:E70)
F11=B11+D11
F12=B12+D12
F13=B13+D13
F14=B14+D14
F15=B15+D15
F16=B16+D16
F17=B17+D17
F18=B18+D18
F19=B19+D19
F20=B20+D20
F68=B68+D68
F69=B69+D69
F70=B70+D70


At the end of the loan the person should have repaid the full $19,250.38. I hope this clarifies things.
 
Upvote 0
so $19,250.38 so the monthly payment does not seem to be correct.

Your future value formula is not taking into account the ongoing reduction of principle that is happening monthly as payments are made. In other words, that is the future value of the loan if there are no payments for 60 months, and then it is all paid off at once after 60 months. But In reality since the loan balance is reduced monthly the total interest is somewhat less as well.

A complete amortization table can test the results for you, as follows (final balance is off by ten cents which is expected since there are all kinds of rounding issues to consider here):

--------------------------------------------------------
Period    Balance    Interest    Payment    New Balance
--------------------------------------------------------
1    15000          62.5           283.07    14779.43   
2    14779.43       61.58095833    283.07    14557.94096
3    14557.94096    60.65808733    283.07    14335.52905
4    14335.52905    59.73137102    283.07    14112.19042
5    14112.19042    58.8007934     283.07    13887.92121
6    13887.92121    57.86633838    283.07    13662.71755
7    13662.71755    56.92798979    283.07    13436.57554
8    13436.57554    55.98573141    283.07    13209.49127
9    13209.49127    55.03954696    283.07    12981.46082
10   12981.46082    54.08942007    283.07    12752.48024
11   12752.48024    53.13533432    283.07    12522.54557
12   12522.54557    52.17727321    283.07    12291.65284
13   12291.65284    51.21522018    283.07    12059.79806
14   12059.79806    50.2491586     283.07    11826.97722
15   11826.97722    49.27907176    283.07    11593.18629
16   11593.18629    48.30494289    283.07    11358.42124
17   11358.42124    47.32675516    283.07    11122.67799
18   11122.67799    46.34449164    283.07    10885.95248
19   10885.95248    45.35813535    283.07    10648.24062
20   10648.24062    44.36766925    283.07    10409.53829
21   10409.53829    43.3730762     283.07    10169.84137
22   10169.84137    42.37433902    283.07    9929.145704
23   9929.145704    41.37144043    283.07    9687.447145
24   9687.447145    40.3643631     283.07    9444.741508
25   9444.741508    39.35308962    283.07    9201.024597
26   9201.024597    38.33760249    283.07    8956.2922
27   8956.2922      37.31788417    283.07    8710.540084
28   8710.540084    36.29391702    283.07    8463.764001
29   8463.764001    35.26568334    283.07    8215.959684
30   8215.959684    34.23316535    283.07    7967.12285
31   7967.12285     33.19634521    283.07    7717.249195
32   7717.249195    32.15520498    283.07    7466.3344
33   7466.3344      31.10972667    283.07    7214.374127
34   7214.374127    30.05989219    283.07    6961.364019
35   6961.364019    29.00568341    283.07    6707.299702
36   6707.299702    27.94708209    283.07    6452.176784
37   6452.176784    26.88406993    283.07    6195.990854
38   6195.990854    25.81662856    283.07    5938.737483
39   5938.737483    24.74473951    283.07    5680.412222
40   5680.412222    23.66838426    283.07    5421.010607
41   5421.010607    22.58754419    283.07    5160.528151
42   5160.528151    21.50220063    283.07    4898.960351
43   4898.960351    20.4123348     283.07    4636.302686
44   4636.302686    19.31792786    283.07    4372.550614
45   4372.550614    18.21896089    283.07    4107.699575
46   4107.699575    17.1154149     283.07    3841.74499
47   3841.74499     16.00727079    283.07    3574.682261
48   3574.682261    14.89450942    283.07    3306.50677
49   3306.50677     13.77711154    283.07    3037.213882
50   3037.213882    12.65505784    283.07    2766.798939
51   2766.798939    11.52832891    283.07    2495.257268
52   2495.257268    10.39690528    283.07    2222.584174
53   2222.584174    9.26076739     283.07    1948.774941
54   1948.774941    8.119895588    283.07    1673.824837
55   1673.824837    6.974270153    283.07    1397.729107
56   1397.729107    5.823871278    283.07    1120.482978
57   1120.482978    4.668679075    283.07    842.0816572
58   842.0816572    3.508673571    283.07    562.5203307
59   562.5203307    2.343834711    283.07    281.7941654
60    281.7941654    1.174142356    283.07    -0.101692205
 
Last edited:
Upvote 0
@xenou - If you look at post#3 the OP actual wants to charge interest on the interest plus the loan amount. Collecting 19,250.38. I wouldn't want to take out a loan with the OP.
 
Upvote 0
Hmm not sure. Post three says this:
With the compound interest method, the interest is calculated on the present value of the loan as well as on the accumulated interest.

I don't know what that really means. The present value of a loan is normally the principle + unpaid interest (true) but normally with a loan that includes monthly payments (which is what this is) you also use the monthly payment to pay the accumulated interest with the remaining portion of the payment paying down the principal (which is why eventually the loan gets paid off). There really should be no such thing as unpaid interest in a loan that has monthly payments from the borrower.

I think the OP needs to clarify the loan conditions.


Note that should you in fact desire to have a loan where you pay interest on the balance + accumulated interest for the full period of the loan you could simply divide the future value by the number of periods. This would NOT be the pmt() function of VBA or Excel. It would more like a promissory note, though I thought these types of loans are usually paid off all at once at the end of the loan period.
 
Last edited:
Upvote 0
I think the OP needs to clarify the loan conditions.

Two types of loans are offered to borrowers based on their credit history, among other things. One type uses the reducing balance, which the PMT function does a perfect job of calculating the monthly payment and the actual interest and I was able to get the amortization schedule for that.

The other type uses compound interest. Let's say someone gets a loan and the compounding method is used. If the loan is for $1,000.00 for 5 years at 5% interest per year compounding monthly, at the end of 5 years, the total interest would be $276.28 and the person would have repaid $1,276.28 in total. With the reducing balance method (PMT), the interest would be $132.27, and the person would have repaid $1,132.27 in total.

The FV VBA function will give me the total at the end of the 5 years, but I need to first determine the payment amount to use that function. So I was looking for a way to determine what the monthly payment and interest on a loan should be using compounding interest.

Hope this clarifies it for you.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

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