Monthly Loan Payment Using Compound Interest

[koolwaters]

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.

 

[AhoyNC]

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
	A	B	C	D	E
1	Loan Data
2	Original Principal	$ 15,000
3	Loan Term (Years)	5
4	Annual Interest Rate	5.00%
5	Payments per Year	12
6	Payment	$283.07
7
8	Month	Payment	Interest	Principal	Balance
9	0				15,000.00
10	1	283.07	62.50	220.57	14,779.43
11	2	283.07	61.58	221.49	14,557.94
12	3	283.07	60.66	222.41	14,335.53
13	4	283.07	59.73	223.34	14,112.20
14	5	283.07	58.80	224.27	13,887.93
15	6	283.07	57.87	225.20	13,662.73
16	7	283.07	56.93	226.14	13,436.59
17	8	283.07	55.99	227.08	13,209.50
18	9	283.07	55.04	228.03	12,981.47
19	10	283.07	54.09	228.98	12,752.50
20	11	283.07	53.14	229.93	12,522.56
21	12	283.07	52.18	230.89	12,291.67
Sheet

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A10	1. / Formula is =$A10>($B$3*$B$5)	Abc
A10	2. / Formula is =$A10=($B$3*$B$5)	Abc
B10	1. / Formula is =$A10>($B$3*$B$5)	Abc
B10	2. / Formula is =$A10=($B$3*$B$5)	Abc
C10	1. / Formula is =$A10>($B$3*$B$5)	Abc
C10	2. / Formula is =$A10=($B$3*$B$5)	Abc
D10	1. / Formula is =$A10>($B$3*$B$5)	Abc
D10	2. / Formula is =$A10=($B$3*$B$5)	Abc
E10	1. / Formula is =$A10>($B$3*$B$5)	Abc
E10	2. / Formula is =$A10=($B$3*$B$5)	Abc
A11	1. / Formula is =$A10>($B$3*$B$5)	Abc
A11	2. / Formula is =$A10=($B$3*$B$5)	Abc
B11	1. / Formula is =$A10>($B$3*$B$5)	Abc
B11	2. / Formula is =$A10=($B$3*$B$5)	Abc
C11	1. / Formula is =$A10>($B$3*$B$5)	Abc
C11	2. / Formula is =$A10=($B$3*$B$5)	Abc
D11	1. / Formula is =$A10>($B$3*$B$5)	Abc
D11	2. / Formula is =$A10=($B$3*$B$5)	Abc
E11	1. / Formula is =$A10>($B$3*$B$5)	Abc
E11	2. / Formula is =$A10=($B$3*$B$5)	Abc
A12	1. / Formula is =$A10>($B$3*$B$5)	Abc
A12	2. / Formula is =$A10=($B$3*$B$5)	Abc
B12	1. / Formula is =$A10>($B$3*$B$5)	Abc
B12	2. / Formula is =$A10=($B$3*$B$5)	Abc
C12	1. / Formula is =$A10>($B$3*$B$5)	Abc
C12	2. / Formula is =$A10=($B$3*$B$5)	Abc
D12	1. / Formula is =$A10>($B$3*$B$5)	Abc
D12	2. / Formula is =$A10=($B$3*$B$5)	Abc
E12	1. / Formula is =$A10>($B$3*$B$5)	Abc
E12	2. / Formula is =$A10=($B$3*$B$5)	Abc
A13	1. / Formula is =$A10>($B$3*$B$5)	Abc
A13	2. / Formula is =$A10=($B$3*$B$5)	Abc
B13	1. / Formula is =$A10>($B$3*$B$5)	Abc
B13	2. / Formula is =$A10=($B$3*$B$5)	Abc
C13	1. / Formula is =$A10>($B$3*$B$5)	Abc
C13	2. / Formula is =$A10=($B$3*$B$5)	Abc
D13	1. / Formula is =$A10>($B$3*$B$5)	Abc
D13	2. / Formula is =$A10=($B$3*$B$5)	Abc
E13	1. / Formula is =$A10>($B$3*$B$5)	Abc
E13	2. / Formula is =$A10=($B$3*$B$5)	Abc
A14	1. / Formula is =$A10>($B$3*$B$5)	Abc
A14	2. / Formula is =$A10=($B$3*$B$5)	Abc
B14	1. / Formula is =$A10>($B$3*$B$5)	Abc
B14	2. / Formula is =$A10=($B$3*$B$5)	Abc
C14	1. / Formula is =$A10>($B$3*$B$5)	Abc
C14	2. / Formula is =$A10=($B$3*$B$5)	Abc
D14	1. / Formula is =$A10>($B$3*$B$5)	Abc
D14	2. / Formula is =$A10=($B$3*$B$5)	Abc
E14	1. / Formula is =$A10>($B$3*$B$5)	Abc
E14	2. / Formula is =$A10=($B$3*$B$5)	Abc
A15	1. / Formula is =$A10>($B$3*$B$5)	Abc
A15	2. / Formula is =$A10=($B$3*$B$5)	Abc
B15	1. / Formula is =$A10>($B$3*$B$5)	Abc
B15	2. / Formula is =$A10=($B$3*$B$5)	Abc
C15	1. / Formula is =$A10>($B$3*$B$5)	Abc
C15	2. / Formula is =$A10=($B$3*$B$5)	Abc
D15	1. / Formula is =$A10>($B$3*$B$5)	Abc
D15	2. / Formula is =$A10=($B$3*$B$5)	Abc
E15	1. / Formula is =$A10>($B$3*$B$5)	Abc
E15	2. / Formula is =$A10=($B$3*$B$5)	Abc
A16	1. / Formula is =$A10>($B$3*$B$5)	Abc
A16	2. / Formula is =$A10=($B$3*$B$5)	Abc
B16	1. / Formula is =$A10>($B$3*$B$5)	Abc
B16	2. / Formula is =$A10=($B$3*$B$5)	Abc
C16	1. / Formula is =$A10>($B$3*$B$5)	Abc
C16	2. / Formula is =$A10=($B$3*$B$5)	Abc
D16	1. / Formula is =$A10>($B$3*$B$5)	Abc
D16	2. / Formula is =$A10=($B$3*$B$5)	Abc
E16	1. / Formula is =$A10>($B$3*$B$5)	Abc
E16	2. / Formula is =$A10=($B$3*$B$5)	Abc
A17	1. / Formula is =$A10>($B$3*$B$5)	Abc
A17	2. / Formula is =$A10=($B$3*$B$5)	Abc
B17	1. / Formula is =$A10>($B$3*$B$5)	Abc
B17	2. / Formula is =$A10=($B$3*$B$5)	Abc
C17	1. / Formula is =$A10>($B$3*$B$5)	Abc
C17	2. / Formula is =$A10=($B$3*$B$5)	Abc
D17	1. / Formula is =$A10>($B$3*$B$5)	Abc
D17	2. / Formula is =$A10=($B$3*$B$5)	Abc
E17	1. / Formula is =$A10>($B$3*$B$5)	Abc
E17	2. / Formula is =$A10=($B$3*$B$5)	Abc
A18	1. / Formula is =$A10>($B$3*$B$5)	Abc
A18	2. / Formula is =$A10=($B$3*$B$5)	Abc
B18	1. / Formula is =$A10>($B$3*$B$5)	Abc
B18	2. / Formula is =$A10=($B$3*$B$5)	Abc
C18	1. / Formula is =$A10>($B$3*$B$5)	Abc
C18	2. / Formula is =$A10=($B$3*$B$5)	Abc
D18	1. / Formula is =$A10>($B$3*$B$5)	Abc
D18	2. / Formula is =$A10=($B$3*$B$5)	Abc
E18	1. / Formula is =$A10>($B$3*$B$5)	Abc
E18	2. / Formula is =$A10=($B$3*$B$5)	Abc
A19	1. / Formula is =$A10>($B$3*$B$5)	Abc
A19	2. / Formula is =$A10=($B$3*$B$5)	Abc
B19	1. / Formula is =$A10>($B$3*$B$5)	Abc
B19	2. / Formula is =$A10=($B$3*$B$5)	Abc
C19	1. / Formula is =$A10>($B$3*$B$5)	Abc
C19	2. / Formula is =$A10=($B$3*$B$5)	Abc
D19	1. / Formula is =$A10>($B$3*$B$5)	Abc
D19	2. / Formula is =$A10=($B$3*$B$5)	Abc
E19	1. / Formula is =$A10>($B$3*$B$5)	Abc
E19	2. / Formula is =$A10=($B$3*$B$5)	Abc
A20	1. / Formula is =$A10>($B$3*$B$5)	Abc
A20	2. / Formula is =$A10=($B$3*$B$5)	Abc
B20	1. / Formula is =$A10>($B$3*$B$5)	Abc
B20	2. / Formula is =$A10=($B$3*$B$5)	Abc
C20	1. / Formula is =$A10>($B$3*$B$5)	Abc
C20	2. / Formula is =$A10=($B$3*$B$5)	Abc
D20	1. / Formula is =$A10>($B$3*$B$5)	Abc
D20	2. / Formula is =$A10=($B$3*$B$5)	Abc
E20	1. / Formula is =$A10>($B$3*$B$5)	Abc
E20	2. / Formula is =$A10=($B$3*$B$5)	Abc
A21	1. / Formula is =$A10>($B$3*$B$5)	Abc
A21	2. / Formula is =$A10=($B$3*$B$5)	Abc
B21	1. / Formula is =$A10>($B$3*$B$5)	Abc
B21	2. / Formula is =$A10=($B$3*$B$5)	Abc
C21	1. / Formula is =$A10>($B$3*$B$5)	Abc
C21	2. / Formula is =$A10=($B$3*$B$5)	Abc
D21	1. / Formula is =$A10>($B$3*$B$5)	Abc
D21	2. / Formula is =$A10=($B$3*$B$5)	Abc
E21	1. / Formula is =$A10>($B$3*$B$5)	Abc
E21	2. / Formula is =$A10=($B$3*$B$5)	Abc

 

[koolwaters]

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.

 

[AhoyNC]

$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
	A	B	C	D
1			Monthly
2	FV =	19,250.38
3	rate (annually)	5.00%	0.004167
4	Years	5	60
5
6	Payment / month	283.07
7
8	Check using FV function	$19,250.38
9
10	Month	Payment	Interest	Balance
11	0			15,000.00
12	1	283.07	62.50	15,062.50
13	2	283.07	62.76	15,125.26
14	3	283.07	63.02	15,188.28
15	4	283.07	63.28	15,251.57
16	5	283.07	63.55	15,315.12
17	6	283.07	63.81	15,378.93
18	7	283.07	64.08	15,443.01
19	8	283.07	64.35	15,507.35
20	9	283.07	64.61	15,571.97
21	10	283.07	64.88	15,636.85
69	58	283.07	79.22	19,090.96
70	59	283.07	79.55	19,170.50
71	60	283.07	79.88	19,250.38
Sheet

 

[koolwaters]

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
	A	B	C	D	E	F
1			Monthly
2	FV=	$19,250.38
3	Rate (annually)	5%	0.004166667
4	Years	5	60
5
6	Payment per Mth	$283.07
7
8	Check Using FV Function	$19,250.38
9
10	Payment #	Beginning Balance	Amoutn Due	Interest	Principal	Ending Balance
11	1	15,000.00	283.07	62.50	220.57	15,062.50
12	2	15,062.50	283.07	62.76	220.31	15,125.26
13	3	15,125.26	283.07	63.02	220.05	15,188.28
14	4	15,188.28	283.07	63.28	219.79	15,251.57
15	5	15,251.57	283.07	63.55	219.52	15,315.12
16	6	15,315.12	283.07	63.81	219.26	15,378.93
17	7	15,378.93	283.07	64.08	218.99	15,443.01
18	8	15,443.01	283.07	64.35	218.72	15,507.35
19	9	15,507.35	283.07	64.61	218.46	15,571.97
20	10	15,571.97	283.07	64.88	218.19	15,636.85
68	58	19,011.74	283.07	79.22	203.85	19,090.96
69	59	19,090.96	283.07	79.55	203.52	19,170.50
70	60	19,170.50	283.07	79.88	203.19	19,250.38
71				4,250.38	12,733.82
72	Total Interest & Principal			16,984.20
73	Difference			(2,266.18)
Sheet1

Cell Formulas
Range	Formula
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.

 

[xenou]

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

 

[AhoyNC]

@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.

 

[xenou]

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.

 

[koolwaters]

The post was not based on whether or not you would want take out a loan or not.

 

[koolwaters]

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.