A mortgage calculator made using excel can come in handy when you need to do quick analysis of how a mortgage fits in with your overall finances. Over the weekend, I designed a mortgage calculator in excel that calculates how much you need to pay for your mortgage.
The nice thing about this spreadsheet based calculator is that all you have to do is to plug in the loan amount, the rate of interest and the tenure and it will calculate everything else for you. In addition to these basic options, there are a couple of other advanced options that help you analyze how a mortgage will work out for you. You can plug in the part prepayments to your mortgage – either every month or year or even at specific intervals during the tenure – and see how the loan amount reduces over a period of time due to those prepayments. Actually that’s not all. If you wish, you can further analyze the return on your house by comparing it with an alternate investment done at a specific rate of interest. The calculator will tell you whether investing in a house will make economic value for you or not. It also allows you to calculate the impact of the yearly property tax and the benefit from the income tax reduction on the return that you generate from buying a house. Additionally, one of the strong long term benefits of buying a house is in the form of rentals. The mortgage calculator allows you to plug in the monthly rentals from a property and check the impact those rentals have on the net cash outflow in the form of monthly EMI’s. The calculator also allows you to assign a rate of appreciation to the property value and the rentals every year so that the resulting cash flows are closer to reality over the long term.
This excel based mortgage calculator also shows you the loan amortization and increase in property value over a period of time using a chart. It plots the decrease in principal amount outstanding against the increase in property value and the build-up of your own equity in your house. By comparing the return with an alternate investment with the returns that you generate from the mortgage on the house, you can make a quick decision whether taking a mortgage on a house makes more sense than keeping the funds in a fixed deposit or some other alternate investment.
Download the mortgage calculator in excel here