Excel. Calculating the amount of principle paid down at a certain period

I am trying to calculate the amount of principle that would have been paid down at year 20 of a 30 year 4.25% loan of 313,000. I converted everything to months.

=PPMT(4.25%/12, 240, 30*12, 313000, 0, 0)

Which returns ($1,003.86) which can't be right. What am I doing wrong.

Thanks,

Steve
steveurichAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
SteveConnect With a Mentor Commented:
=PPMT(0.0425/12,240,30*12,313000,0,0)

This is the principle paid for the single payment

The total paid at month 240 = $162,686.41

Attached is the full 30 year calculation.
Payments.xlsx
0
 
SteveConnect With a Mentor Commented:
Attached is the file using the PPMT function to determine how much has been paid off from the principal over time.

you could use a single array formula...

=SUM(PPMT(0.0425/12,ROW(A1:A240),30*12,313000,0,0))
then enter using [ctrl]+[shift]+[enter]
Payments.xlsx
0
 
Patrick MatthewsCommented:
The_Barman seems to have this one well in hand, especially with that very clever array formula :)

You might also want to play around with the sample workbook in this article, especially if you want to model the impact of making additional principal payments:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3331-Fixed-Rate-Loan-Amortization-Schedule-with-Optional-Extra-Principal-Payments.html
0
 
SteveCommented:
Hi there Patrick, I think I owe you a beer as have been using Dictionaries all over the place.
Cheers :)
0
All Courses

From novice to tech pro — start learning today.