In the last post, we learnt about getting maximum and minimum value from set of values in Excel. In this post, we shall learn how to calculate payment for a loan based on monthly payment and to a specific interest rate. This can be done using PMT function of Excel.
To understand how it works, we shall calculate the monthly payment of a term loan of 14,50,000 for a period of 20 years on 9.75% interest rate.
Get monthly installments for loan
The PMT function helps in getting payment amount (installment) of a loan. It accepts at least 3 parameters and they are
The same calculation if we do for Nper 180 (ie. 15 years) we get different result (notice 3rd row). Just for the sake of knowing how much you end up paying more, we have kept the F and G column.
Get interest rate of the loan
Similar to above, if we want to know what would be the interest rate, if we want to borrow certain amount, for certain period of time and we want to pay a certain amount every month then we can use RATE function.
In B4 cell, we have '=RATE(C2,A2, D2, E2)' formula where
The result would look like this. In general result come in full number, simply use cell formatting to display decimal numbers.
Get period (in months) of a loan
Let's say we want to know the period (in months) we will have to pay if we borrow certain amount at certain interest rate and we want to pay certain amount every month, then we can use NPER function.
In C4 cell, we have '=NPER(B2,A2,D2,E2)' formula where
Get how much we can borrow
Let's assume that you want to know that if you pay certain amount every month, till certain period of months with certain interest rate then how much amount you can borrow? We will need to use PV function.
In D4 cell we have '=PV(B2,C2,A2,E2)' formula where
Get if you can pay off loan after paying only certain amount every month
Let's assume a scenario where you want to know that what if you pay only certain amount every month, with a certain interest rate for a certain amount of loan, whether you will be able to pay off the entire loan? To know this we can use FV function
In E4 cell, we have '=FV(B2,C2, A2,D2)' formula where
Notice that slight change (only 753.49 less amount) in the Payment amount (installment) results in huge left over amount (5,53,971.31).
Here you must be knowing the magic of cummulative interest. The same applies when you save money every month !Views: 4136 | Post Order: 33