how can i find sum of principle portions from multiple monthly payments for an annuity? e.g. $400,000 home mortage, 20 years ammortization, 4% pa, paid monthly. PMT gives principl and interest every month, but i want to find out just sum of principal covered e.g. first 60 months, or second 40 months, etc? thanks!
Use the CUMPRINC function. However, the "rate" parameter must be a monthly interest rate. The method for converting an annual rate to a monthly rate varies with the region.Originally Posted by excsal
For a US loan, use:
=-CUMPRINC(4%/12,20*12,400000,1,60,0)
or
=-CUMPRINC(4%/12,20*12,400000,41,80,0)
For an EU loan, use:
=-CUMPRINC(RATE(12,0,-1,1+4%),20*12,400000,1,60,0)
For a Canadian loan, use:
=-CUMPRINC(RATE(6,0,-1,1+4%/2),20*12,400000,1,60,0)
Note: RATE(12,0,-1,1+4%) can also be written (1+4%)^(1/12)-1.
In 2003 CUMPRINC function may not be available.Pl see attached file with formula.
Last edited by kvsrinivasamurthy; 08-13-2014 at 06:13 AM.
Gee, that's usually my line! I just tried to KISS for once.
Actually, I usually recommend against using CUMPRINC because it does not take real-world constraints into account. Specifically, it does not round the payment (of course).
Instead, I prefer the formula demonstrated in C9 and shown in D9 below.
A
B C D 1
Loan
400,000.00
2 Original loan term 240 months =20*12 3 Actual loan term 240 months =ROUND(NPER(B4,B5,B1),0) 4 Interest rate 0.3306% monthly =RATE(6,0,-1,1+4%/2) 5 Monthly pmt -2,416.99
=ROUNDUP(PMT(B4,B2,B1),2) 6 Last pmt -2,415.48
=FV(B4,B3-1,B5,B1)*(1+B4) 7
8 Start Pmt# End Pmt# Princ Paid
9 1 60 72,512.60 =FV($B$4,B9,$B$5,$B$1)-FV($B$4,A9-1,$B$5,$B$1) 10 41 80 53,324.78
11 1 239 397,592.48
12 240 240 2,407.52 =-FV(B4,B3-1,B5,B1)
Formulas in column B and column C are shown in column D.
The table demonstrates the calculation of the Canadian monthly interest rate in B4 (least intuitive), shown in D4.
Because the payment is rounded in B5 (rounded up in this case; see D5), the actual number of payments might be less than the original.
So the actual number of payments are recalculated in B3 (see D3).
For the same reason, the last payment is usually different. The last payment is calculated in B6 (see D6).
Last edited by joeu2004; 08-13-2014 at 12:00 PM. Reason: cosmetic
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks