If you have taken a loan and want to calculate your EMI, then it is very easy in Excel. You can create your own Mortgage Calculator with Microsoft Excel. Let’s see the steps.


1. Open Microsoft Excel.
2. Let’s give a heading name, click in a cell, and type Mortgage Calculator.
3. In the next row, type Loan Amount, and then in the next row type Annual Interest.
4. Click in column C, next to the Loan Amount cell, and type your total loan amount. Like here for demo purposes, I am typing $500000,
5. If the dollar sign is not visible then right-click on the same cell and from the drop-down list click on the Format Cells.
6. In the Number tab, select the Currency option and make sure that the $ sign is selected in Symbol dropdown on the right side.
7. Click on the OK button.
8. Now, write your Annual Interest. I am clicking on the C4 cell and typing 8% as interest.
9. Below the Annual Interest row, we will write the Total Loan Duration.
10. In column C and row 5, I typed 20 years as the total loan duration period.
11. In the next row 6, type how many payments you will do in a year. Like here, I am putting number 12 in the C column which means I will repay my loan installments every month.
12. It’s time to calculate the total payment months for the loan. To do so, click the C7 row and type the following formula =C6*C5, and click on the tick in the formula bar.
13. As a result, we can see that we have to repay our loan in 240 months in 20 years.
14. Now, we will calculate the formula for per month installment payment.
15. Type Payment per month in the next row of Total Payment Months.
16. Click in C column 8 row cell and type the following formula
–PMT(C4/C6,C7,C3,0) and click on the tick icon.
17. This means our monthly payment will be $4,182.20
18. We will now calculate how much total money we will repay against our loan.
19. Click in row 9 of the B column cell and type Sum of payments.
20. In the C9 cell and type the formula =C8*C7, then click on the tick icon. As a result, we will repay the total payment of $1,003,728.08
21. If you want to know, how much total interest payment you will pay, then click the next row and type Interest Cost, click the C10 cell and type the formula =C9-C3, and click on the tick icon.
22. Our Mortgage calculator is ready in Excel.

Hope this was easy. Have a nice day!
Please subscribe to our channel.






Disclaimer: Content in this video is provided on an "as is" basis with no express or implied warranties whatsoever. Any reference of any third party logos, brand names, trademarks, services marks, trade names, trade dress and copyrights shall not imply any affiliation to such third parties, unless expressly specified

свернуть