Sometimes we need to calculate the Loan Amount in Excel to borrow loan from Bank or some others company. There are many Banks and Financial Companies offered many kinds of Schemes. But before choosing anyone of them, you can simply calculate these scheme through Excel with few built-in functions, which will give you the real scenario. But before starting calculation you need to understand few Financial Terms.
Some Financial Terms:
PV (Present Value): This amount is basic. No interest or Tax included here. It is also known as Principal Value.
FV (Future value): Based on Time Value of Money (TVM), it calculated for a specific period of time. It can be greater than PV or less than PV.
Rate (Interest Rate %): It is the interest the set by the Bank. The Bank will take this interest as additional amount from your principal amount. Bank will mention it in contract as Annually. Make it sure from the Bank first. Assume that, Bank said Interest Rate is 12% Annually. Now you can convert the 12% into any period like Weekly interest rate (12%/52), Monthly interest rate (12%/12), Yearly interest rate (12%/1) and so on.
Nper (Number of Period): The number of period. The Bank will mention it on the contract that, what is the period number to clear your loan. It can be Weekly (52), Bi-Weekly (26), Semi-Monthly (24), Monthly (12), Bi-Monthly (6), Quarterly (4), Yearly (1).
Per (Period): This period different from Nper. It depends on only you. Suppose that, the Bank said that, you must clear your loans (Tk. 100000) within 2 years (that means 24 months if it was monthly payment), but you wish to settle the loan within 14 months. This 14 months is Per.
On the other hand, it would be very clear for this term is, PER is a term to calculate the Nth term amount. It would be interest rate, principal amount or loan amount. If you increase this Nth term, then your amount will be decreasing. For example, you are have calculated an interest amount for 12 years, then you simply type 12 years. But you need to pay the Bank from the 1st month, so use 1 here to calculate the 1st years interest amount, then while your payment done, your loan amount becomes smaller and then again calculate the 2nd years interest amount by using 2 here. You will see the interest rate becomes smaller if year passed.
Payment Day: Bank will ask you that, which day you wish to pay in a given term? Assume that, you have 12 months to clear the loan. In each month which day you wish to pay, 1st day or last day of the month? Based on this question Interest Amount and Principal Amount will increase. If you pay 1st day of each term (month), then your Interest Amount and Principal Amount will be minimum, but if you pay your monthly payment at the last day of each month, then Interest Amount and Principal Amount will increase. So, my recommendation is, try to pay 1st day of the month/term.
Example:
Assume that, Mr. Hamim borrowed $500,000 from a Bank for 3 years, with an annual interest rate of 12.50%. Now calculate, what is the Monthly Payment, Interest Rate Per Month, Principal Amount Per Month.
Functions Used:
PMT: This function is used to calculate the termly payment amount including Interest Amount and Principal Amount but without Tax amount. PMT = (Interest Amount + Principal Amount). This function will answer you how much money you need to pay per term to Bank.
PPMT: This function is used to calculate only the Principal Amount, that you need to pay per term to Bank. Tax is not included here as PMT.
IPMT: This function is used to calculate only the Interest Amount, that you need to pay per term to Bank. Tax is not included here.
PMT Calculation:
To calculate the Monthly Payment (Installment Amount) including the Interest Amount and the Principal Amount you need to use this function. As per example used here, plot the amount and conditions like below image:
To calculate the PMT in C13 cell use the below formula:
In the above function, C4 = Interest Rate 12.50% divided by C6 = 12 is a meaning of monthly interest rate. The calculation will returns the result like below image in C13 cell without Tax. But included Interest Amount and Principal Amount. To get the perfect monthly installment amount, you need to add Tax amount as Bank will told you with this PMT calculated amount.
Please note that, the amount is automatically formatted as Red and Negative. It's because of Excel knows 2 things, Cash Flow IN or Money Receive from Bank is mentioned as +. But if you pay it to others that means Cash Flow OUT or Money Payment to Bank is mentioned as (-) Negative and Red.
PPMT Calculation:
To calculate the Principal Amount only in a Monthly Payment (Installment Amount) you need to use this function. As per example used here, in C14 cell use the below formula to calculate the principal amount of the last installment:
In the above function, C10 is the number of term which you wish to clear the loan. The result returns below in C14 cell is only Principal Amount. No Tax and Interest amount included here.
IPMT Calculation:
To calculate the Interest Amount only in a Monthly Payment (Installment Amount) you need to use this function. As per example used here, in C15 cell use the below formula to calculate the interest amount of the last installment:
The above formula will provide you the below result:
And now, If you SUM the PPMT and IPMT then you will get the PMT. So, it is clear that If you have PMT and IPMT or PPMT then you can easily get Principal Amount or Interest Amount by subtracts it from PMT.
This equations are very simple to understand the PMT, PPMT and IPMT.
Some Financial Terms:
PV (Present Value): This amount is basic. No interest or Tax included here. It is also known as Principal Value.
FV (Future value): Based on Time Value of Money (TVM), it calculated for a specific period of time. It can be greater than PV or less than PV.
Rate (Interest Rate %): It is the interest the set by the Bank. The Bank will take this interest as additional amount from your principal amount. Bank will mention it in contract as Annually. Make it sure from the Bank first. Assume that, Bank said Interest Rate is 12% Annually. Now you can convert the 12% into any period like Weekly interest rate (12%/52), Monthly interest rate (12%/12), Yearly interest rate (12%/1) and so on.
Nper (Number of Period): The number of period. The Bank will mention it on the contract that, what is the period number to clear your loan. It can be Weekly (52), Bi-Weekly (26), Semi-Monthly (24), Monthly (12), Bi-Monthly (6), Quarterly (4), Yearly (1).
Per (Period): This period different from Nper. It depends on only you. Suppose that, the Bank said that, you must clear your loans (Tk. 100000) within 2 years (that means 24 months if it was monthly payment), but you wish to settle the loan within 14 months. This 14 months is Per.
On the other hand, it would be very clear for this term is, PER is a term to calculate the Nth term amount. It would be interest rate, principal amount or loan amount. If you increase this Nth term, then your amount will be decreasing. For example, you are have calculated an interest amount for 12 years, then you simply type 12 years. But you need to pay the Bank from the 1st month, so use 1 here to calculate the 1st years interest amount, then while your payment done, your loan amount becomes smaller and then again calculate the 2nd years interest amount by using 2 here. You will see the interest rate becomes smaller if year passed.
Payment Day: Bank will ask you that, which day you wish to pay in a given term? Assume that, you have 12 months to clear the loan. In each month which day you wish to pay, 1st day or last day of the month? Based on this question Interest Amount and Principal Amount will increase. If you pay 1st day of each term (month), then your Interest Amount and Principal Amount will be minimum, but if you pay your monthly payment at the last day of each month, then Interest Amount and Principal Amount will increase. So, my recommendation is, try to pay 1st day of the month/term.
Example:
Assume that, Mr. Hamim borrowed $500,000 from a Bank for 3 years, with an annual interest rate of 12.50%. Now calculate, what is the Monthly Payment, Interest Rate Per Month, Principal Amount Per Month.
Functions Used:
PMT: This function is used to calculate the termly payment amount including Interest Amount and Principal Amount but without Tax amount. PMT = (Interest Amount + Principal Amount). This function will answer you how much money you need to pay per term to Bank.
PPMT: This function is used to calculate only the Principal Amount, that you need to pay per term to Bank. Tax is not included here as PMT.
IPMT: This function is used to calculate only the Interest Amount, that you need to pay per term to Bank. Tax is not included here.
PMT Calculation:
To calculate the Monthly Payment (Installment Amount) including the Interest Amount and the Principal Amount you need to use this function. As per example used here, plot the amount and conditions like below image:
Image 1: Loan condition plotting in Excel
To calculate the PMT in C13 cell use the below formula:
=PMT(C4/C6,C5,C3,C9,1)
In the above function, C4 = Interest Rate 12.50% divided by C6 = 12 is a meaning of monthly interest rate. The calculation will returns the result like below image in C13 cell without Tax. But included Interest Amount and Principal Amount. To get the perfect monthly installment amount, you need to add Tax amount as Bank will told you with this PMT calculated amount.
Image 2: PMT = $16,554.37 (including Interest Amount + Principal Amount)
Please note that, the amount is automatically formatted as Red and Negative. It's because of Excel knows 2 things, Cash Flow IN or Money Receive from Bank is mentioned as +. But if you pay it to others that means Cash Flow OUT or Money Payment to Bank is mentioned as (-) Negative and Red.
PPMT Calculation:
To calculate the Principal Amount only in a Monthly Payment (Installment Amount) you need to use this function. As per example used here, in C14 cell use the below formula to calculate the principal amount of the last installment:
=PPMT(C4/C6,C10,C5,C3,C9,1)
In the above function, C10 is the number of term which you wish to clear the loan. The result returns below in C14 cell is only Principal Amount. No Tax and Interest amount included here.
Image 3: PPMT = $16,383.71 (Without Tax and Interest Amount)
IPMT Calculation:
To calculate the Interest Amount only in a Monthly Payment (Installment Amount) you need to use this function. As per example used here, in C15 cell use the below formula to calculate the interest amount of the last installment:
=IPMT(C4/C6,C10,C5,C3,C9,1)
The above formula will provide you the below result:
Image 4: IPMT = $170.66 (Only interest amount)
And now, If you SUM the PPMT and IPMT then you will get the PMT. So, it is clear that If you have PMT and IPMT or PPMT then you can easily get Principal Amount or Interest Amount by subtracts it from PMT.
PMT = PPMT + IPMT
PPMT = PMT - IPMT
IPMT = PMT - PPMT
This equations are very simple to understand the PMT, PPMT and IPMT.