2018-01-08
In the previous post we have covered how you can use Apple’s Numbers application to calculate mortgage payment. In this post, I will show you how you can do similar thing with Microsoft Excel.
I have been using Microsoft Excel for long time, so I am still more comfortable using Microsoft Excel. I also feel that Microsoft Excel still have some edge compared to other spreadsheet software I have been using. I think Microsoft Excel still has the most powerful function for spreadsheet compared to other software. However, mortgage calculator is something you can do easily with different spreadsheet software.
In this post, I will also try to illustrate differently how the mortgage calculator actually works. I will try to include some of the formula I used to give you some ideas how things work. I think you should be able to create your own Mortgage/Loan Calculator by following this post. However, if you want to get the spreadsheet I created in this post, you can click on Subscribe button and I will provide a link to download the Microsoft Excel Spreadsheet to those who subscribe.
You can also check out the video that shows how I create the Microsoft Excel spreadsheet shown in this post.
First, let’s start with what looks like Mortgage Detail in Apple’s Numbers. As in the previous post, you need to have 4 type of information available:
You can make the mortgage detail like shown below. First, you enter the Yearly interest, Purchase price, Percent borrowed, and Period in years. These fields are marked in green in the picture below. Next, we can calculate the Amount borrowed, which will be the Percent borrowed multiplied by Purchase price. You can also compute the Down payment or Deposit by which will be (100%-Percent borrowed) multiply by the Purchase price. The picture below shows an intermediary value for monthly interest and period in months. This is not mandatory as you can enter the formula directly in the payment formula, but I think it help to visualize the calculation for the reader.
The main formula for the monthly payment is using the PMT formula. This is also a kind of formula that a specialized financial calculator can compute for you. To simplify, the PMT formula looks like this:
=PMT(Monthly interest, Period in months, Amount borrowed)
If you notice the formula for the Payment per month below, there are additional 2 parameters with value zero. These parameters are the default value and can be omitted; they simply means there are no more outstanding loan after the end of the loan and the payment are due at the end of each month. Note that the payment is shown as negative value. This simply means that you are paying instead of receiving money.
Under Construction.