Greetings reader,
This post explains you how to build your own EMI table in Microsoft Excel.
There is a formula in Excel called PMT - It will calculate the EMI payable per month.
Screenshot 1.1 - Formula "PMT"
In the above screenshot you can see the requirements of the formula PMT
(1) RATE
- It is the rate of interest of the loan.
- Rate should be per installment. i.e. if installment is payable monthly then rate must be rate per annum / 12 months. If rate of interest per annum is 12% then rate per month is 1%.
(2) NPER
- Nper refers to the number of installments.
(3) PV
- Present Value of money or the amount borrowed.
(4) FV
- In an EMI calculation generally the amount at the end of payment of all installments is '0'. If you want some balance to remain at the end of the tenure you need to type in such amount in the FV field.
(5) Type
| Value | Explanation |
|---|---|
| 0 | Payments are due at the end of the period. (default) |
| 1 | Payments are due at the beginning of the period. |
The screenshot below is an example by which you can create an EMI Table
The formulas used in the above screenshot are as follows
Hope you can now create your own EMI Table.
No comments:
Post a Comment