LinkedIn Learning – Excel: Financial Functions in Depth
Sect. 1
A. Analyzing Loans, Payments & Interest
1. PMT (Calculate a loan payment) –
A) This calculates the amount that needs to be repaid on a loan
B) NOTE: It is also a fundamental function that is important to know for Forecasting
loan payments, Debt calculations, Cash Flows and all other sort of financial
calculations.
C) Process –
1. In Ch. 1 Sheet
2. NOTE: PMT Formula:
=PMT(Rate, NPER, PV, [FV], [Type])
Rate = Interest Rate (C4)
Nper = Term of Loan (C5)
PV = Loan Payment (C6)
FV = (Future Value) Amount Loan remaining (C7)
Type = Beginning or End of period (C8)
3. In cell (C10) enter formula:
=PMT(C4,C5,C6,C7,C8)
NOTE:
For (C7) this is the amount that you want owing at the end of the loan period. In
this case you want to pay back of all of the loan in the 25-year period.
For (C8) this is asking if you want to pay the amount at the beginning or the end
of the period.
4. In cell (C11) for Monthly Repayment –
If you take the amount in (C10) and divide it by 12 that is NOT accurate
The Proper way is in cell (G10) enter formula:
=PMT(G4/12,G5*12)
5. In cell (G10) change the amount to a positive number because it is a cash
payment / cash outflow. To do so add a -1 –
=PMT(G4/12,G5*12)*-1
2. PMT and IPMT (Calculate the Principal and Interest payments) –
A) PMT –
- Total Loan Repayment
- Includes Interest and Principal
