2018-01-08

Mortgage Calculator using Microsoft Excel part 2

In the first part of this post, we have covered how to compute the monthly payment for the mortgage or loan. Notice that in the first part we do not have the monthly data like the one provided by Apple’s Numbers Mortgage Calculator template. In this post, we will show you how to compute the monthly data for your loan. By making the monthly data, I hope this can illustrate how your loan is actually calculated.

Before we go into the spreadsheet, we will go into the basic theory for the financial calculation. First we will talk about interest rate.

When we borrow money, the bank will usually calculate and charge us the interest every month although the bank usually publishes the interest rate as interest rate per year. In financial term, it means the interest is compounded monthly. To simplify in this post, we will assume that the monthly interest rate is the annual interest rate published by the bank divided by the bank. Depending on where you live, the bank may also mention the effective interest rate. We will cover about effective interest rate in another post.

In addition to the interest that you pay, part of the monthly payment will need to pay the remaining balance of your loan or mortgage. Let’s call this loan amount as principal. In short, the monthly payment for your loan will be principal payment plus the interest rate. It means, the remaining principal will be reduced each month. Obviously, the bank should calculate and charge the interest monthly on the remaining principal on that month, not on your initial loan amount.

To summarize the process:

• First the bank will calculate the interest on the remaining principal amount. This will be the interest you need to pay on that month.
• Your monthly payment must pay the interest in full, and the remaining will reduce the amount of principal for next month.
• In the following month, the interest will be calculated based on the new principal amount that has been reduced in the previous month.

Now let’s dive into the Microsoft Excel spreadsheet. From example of part 1 of the post, we have the following data:

• Interest rate of 12% per year, which translate to 1% per month.
• The purchase price is $10,000.00, with 20% deposit or down payment. It means, the initial loan principal is $8,000.00.
• To make the illustration easier, we will set the loan duration to 1 year. It means, we will completely repay the loan in 12 month.
• The payment for each month will be $710.79 to completely pay off the loan in 1 year.

The Microsoft Excel spreadsheet for the monthly data will be as follow. The formulas shown are for the second month. Note that the yearly interest rate are in cell B2, and the monthly payment are in cell B10. To illustrate clearly the process, we sequence the column in the order it is processed.

First we show the remaining principal before any payment made in Column B. We then put the yearly interest rate in column C. Then we calculate the interest payment for each month based on the remaining principal from last month in Column D. Add them together, we have the Balance before payment in Column E. The payment for each month is in Column F. After the payment, the remaining principal will be in the Principal balance in column G, and it will become the Principal before payment in column B for the following month (next row).

The Principal payment in column H is calculated to show you the portion of your payment that goes to the payment of your principal. Generally, I would like to see that significant portion of my monthly payment goes to the principal compare to the interest payment.