Need a formula for loan payment schedule (not PMT) that reflects additional payments made

Originally Posted by alma_gimpy

Here is the math I need; how can I make it work?...

=((E6/1200)+((E6/1200)/(((1+(E6/1200))^E7)-1)))*E5

E5 = $66523.25
E6 = 6.8%
E7= 120 (payments)

This is the math itself… (.0056666666+(.0056666666/((1.0056666666^120)-1)))*66,523.25 and that should = $ 765.55175

Your first formula should be (removing some unneeded parentheses; optional):

=(E6/12 + E6/12 /((1 + E6/12 )^E7 - 1))*E5

But more simply:

=PMT(E6/12,E7,-E5)

formatted as Number, if you wish. (If you format as General, Excel insists on changing the format to Currency every time you edit the formula.)

The key is -E5; that is, representing the present value (loan balance) as a negative number, if you want PMT() to return a positive number.

Interest rate (6.8%) is divided by 12, not 1200, because we want a monthly rate. Note that you wrote 6.8% in E6, not 6.8. So the percentage is already divided by 100. That is, it is the number 0.068, not 6.8.

Originally Posted by alma_gimpy

I want each cell to draw from the total amount paid, and the principal amount remaining, so that I can see the life of my loan. I want a column for my monthly payment, which would stay constant based on the 10 year payment plan, and a column for additional payments, which could be any amount I chose varying from month to month, but I want to see how the additional payments change the remaining balance and the amount of payments left. [....] I figured having the math worked out itself, instead of using the PMT function, was a good start.

Regarding "math" v. PMT, you're talking about apples and oranges. Use PMT to compute the regular monthly payment based on the original amortization of the loan. But use simple arithmetic to calculate the loan balance month by month, which can easily account for additional payments as well as missing payments.

Download "alma loan sched.xls" (click here) [1]. The table below presents the first few lines.

	D	E	F	G	H	I	J	K
1				Pmt#	Reg_Pmt	Addl_Pmt	Int_Pmt	Balance
2								66,523.25
3				1	765.55		376.97	66,134.67
4				2	765.55		374.76	65,743.88
5	Loan	66,523.25		3	765.55		372.55	65,350.88
6	Annual_rate	6.80%		4	765.55		370.32	64,955.65
7	Loan_term	120		5	765.55		368.08	64,558.18
8	Reg_Pmt	765.55		6	765.55		365.83	64,158.46

Formulas:
E8: =ROUND(PMT(E6/12,E7,-E5),2)

K2: =E5
G3: 1
H3: =IF(G3="","",IF(G3=$E$7,ROUND(K2*(1+$E$6/12),2),MIN($E$8,ROUND(K2*(1+$E$6/12),2))))
J3: =IF(G3="","",K2*$E$6/12)
K3: =IF(G3="","",K2+J3-H3-I3)
G4: =IF(OR(G3="",G3=$E$7),"",G3+1)

Copy H3:K3 down though H362:K362 and copy G4 down through G362 to allow for up a 30-year loan with monthly payments.

Enter additional payments into column I. Enter a negative number into column I, or change individual regular payments in column H to reflect short or missing regular payments.

Note that E8 and H3 are rounded to the cent (smallest coin of the realm) to reflect reality. Consequently, even with just regular payments, the last payment might be different. In this example, payment #120 is 765.85, not 765.55.

In contrast, J3 and H3 are purposely not rounded. In reality, each lender has its own rules for handling fractional interest and balance amounts. In my experience, most do not round to the cent. But some do round to a fixed number of decimal places; for example, 6 or 8.

Because the last balance is not rounded to the cent, sometimes it is infinitesimally more or less than zero due to anomalies of 64-bit binary floating-point representation and arithmetic, used by Excel.


-----
[1] https://app.box.com/s/2apdms9rjn9319ubquvl