Today we will build a mortgage payment calculator using excel. But we will not build a boring excel sheet, we will build a mortgage calculator that is easy to play with.
A mortgage payment is a monthly installment that you pay towards a loan. Any mortgage loan will typically have,
- loan amount
- duration of the loan (also called as tenure of mortgage) in years
- interest rate (APR) per year
Given these 3 parameters, we can easily determine the monthly installment amount (this will be the same amount for all months during loan tenure)
We are going to use Excel’s form controls (more on this below) to build a mortgage payment calculator like this:
Why you should not be boring and use the form controls
A form control is a button or check box or scrollbar or some other click-able thing you see in Windows. Do you know that you can add the very same controls to Excel spreadsheet to make the it interactive?
For example,
- instead of asking a user to enter “yes” or “no” in a cell, you can ask them to click a check box.
- instead of taking “age” in a cell, you can use a scroll bar and set the values from 0 to 100.
This way of gathering inputs is more fun, engaging and interactive.
Now that you find form controls hot and attractive, lets proceed and make a house loan payment calculator.
How is mortgage payment calculated?
As I said above, any mortgage (or housing loan) will have 3 parts – loan amount (p), loan tenure (n) and annual interest rate (r).
Given the values of P, N and R, we can find the monthly payments using Excel’s PMT formula like this:
=PMT(R/12,N*12,P)
[Related: PMT formula syntax & examples]
[Related: Amortization Schedule in Excel]
We are dividing interest rate (R) by 12 since R is annual interest rate and we make monthly payments.
We are multiplying loan duration (N) with 12 since we are going to make monthly payments.
Making the mortgage calculator in Excel
We will use scroll-bar controls to take numeric inputs required (P,N and R) for the payment calculation. And we feed these values to PMT formula to find the monthly installment amount.
Step 1: Add a Scroll-bar Control
We will use this scroll bar to take “loan amount” input. To keep it simple, we will ask users to enter input in ‘000s. So, if the loan is $120,000, the input should be 120.
First add a scroll-bar form control to your excel sheet. To do this go to Developer Ribbon > Insert > Scroll-bar Form Control in Excel (related: enable developer toolbar in Excel)
Add a Scroll-bar Control
Once selected, just add the control to spreadsheet by clicking anywhere.
Step 2: Set Properties for this Scroll-bar
To set the properties for the scrollbar control, right click on it and go to “format control” option. Now go to “Control” tab.
Here set minimum and maximum values for the scroll bar. To keep our model simple, just set minimum as 35 and maximum has 500.
Also, select a cell to link the scrollbar. When you do this, excel links the scroll bar to the selected cell. So whenever scroll bar is updated the cell gets updated too (and vice-a-versa). See this illustration:
Step 3: Add Remaining Scroll bars
Repeat the same steps for 2 other scroll bars. One for interest rate and one for loan tenure.
Make sure you set the minimum and maximum values in a reasonable range.
Step 4: Plug the values in to PMT formula
Now that the scroll bars are ready, just write the PMT formula. Assuming you have linked scroll bars like this:
- Loan amount in cell A1
- Interest rate in cell A2
- Loan tenure (years) in cell A3
The formula will be,
=PMT((A2/12)%,A3*12,A1)
Remember, PMT returns value in negative numbers (as it is the amount we need to pay, not get). But you can make it positive (for display purposes) by multiplying it with -1 like this = -PMT((A2/12)%,A3*12,A1)
Step 5: Play with your Model
Now your mortgage payment calculator is ready. You can play with it by testing various combinations and finding monthly payments. You can easily see what happens when you increase loan tenure or decrease interest rate.
Download Excel Mortgage Payment Calculator
Here is the excel mortgage payment calculator file. Download and play with it.
Bonus – Making an Amortization Schedule
You can easily extend this model to add an amortization schedule to see how much of each monthly payment is towards principal and how much is for interest.
- You can calculate principal portion for any month using PPMT formula like this
=PPMT(R/12,M,N*12,P). Here “M” is the month for which you want principal amount. - You can calculate interest portion for any month using IPMT formula like this
=IPMT(R/12,M,N*12,P).
Full tutorial: Loan Amortization Schedule with Excel.
Do you love form controls?
Do you use form controls in your spreadsheets? I find them pretty intuitive and use them wherever I can. I have made many complex spreadsheet models easy to understand and work with by just adding form controls. The beauty is that, they require no programming or anything. You just add them and link them to a cell.
What about you? Do you love form controls? Where do you use them most?
Learn More about Excel Form Controls:
17 Responses to “Budget vs. Actual Profit Loss Report using Pivot Tables”
Good Work, Yogesh & Chandoo! Thanks.
Hi everybody,
first sorry I am late to say something about this topic;actually I was waiting last part
second I am not accountant I am an Engineer
third """"Very Important""" the idea is not about Loss but I am sure it is profit
Based on third it shows:
1- How to use EXCEL
2- How to use pivot TABLES
3- How to collect and arrange DATA
4- How to make reports
Many Thanks
Hi Yogesh and Chandoo,
Thank you for sharing your knowledge!
You guys are great!
thanks chandoo and yogesh, thanks for you lessons, are great!....i have a idea for a budget. I try to do it..... thanks for all
Thanks a lot for sharing the most powerful tool worldwide "knowledge"
Warm greetings from Peru
Hi -
This is a really great article because it's a simple and common thing you'd want to do with a pivot table but not at all obvious how to do it! So - muchas gracias to Chandoo and Yogesh!
One thing - I couldn't get past the group error in the sample file. I would click on ungroup but it didn't seem to have any effect. I'd appreciate it if anybody has any pointers here.
-Juanito
Hi Chandoo
I am also having the group error. Can't seem to ungroup? Appreciate if you explain further on the steps required in order to get to calculated items.
Many thanks and keep up the great work.
Cheers
Adam
Hi Chandoo,
I'm struggling resolving the problem depicted below:
I have a set of data, with (among others) a "Region" field (can be APJ, EMEA, or AMS), and a "Country" field.
Unfortunately, I need to group data by the following 4 Regions: APeJ, Japan, EMEA and AMS.
I first tried to make a pivot with Region and Country in the rows (or columns), and then group Country data as per the above.
Alas, as soon as I have a new Country that appear in my data set, my groupings are broken, and I have to redo the job of ungrouping, grouping etc.
I thought I could try to use calculated item, by adding first a new column to my dataset concatenating Region_Country, and create an "APeJ" calculated item that would sum all the "APJ_*" and substract the "APJ_Japan", but again, no clue, as I can't find a way to use any wild card in those formulas.
Given that I already found extremely helpful tips and tricks in your site that helped me manage that bunch of data, I'm pretty sure you'll have a bright idea on how I can solve that one!
Thanks in advance for your lights!
Hi Catherine...
In such cases, I advice using an additional column in the data itself. You can set-up a grouping table else where with country in first column, region in second column. And then in the data, you can add an extra column and use VLOOKUP to fetch the region based on the country.
Then feed this entire data (with extra column) to pivot table and use the extra column to group the data.
Hi Chandoo,
Thank you for your prompt answer.
I finally came to the same conclusion - after a rest 🙂 . I was probably too tired Friday evening (it was rather late), having spent hours in manipulating all my surveys data so as to pull rolling averages, make nice graphs and so on, and was trying to find a complex solution when there was a simple one.
Thanks again,
Catherine
Hey,
Great post!
I for example have different database structure with the following fields :
Date, Expense, Income, Sum (Income - Expense), Category (Sales, Cost of Goods and etc).
Creating a P&L report for the whole year works great. Including gross margin % and etc.
Though, creating P&L report by QTR/Month is becoming impossible since i get the following error : “This PivotTable report field is grouped. You cannot add calculated item to grouped filed.”
Is there a solution for this kind of problem?
Like Adam and Juanito, I also cannot ungroup.
Would appreciate it if you can add a few more lines and a screenshot or two on where to put the mouse cursor to ungroup.
Hi, I have figured out the ungrouping problem. One of the earlier steps was to group by month, if you pull the month back down to the column then right click and then select ungroup, then pull the month back up so you end up with just data source and budget/actual as the headings, then you can continue on.
To solve the ungroup problem, my method is:
Copy the "data" sheet to a whole new Excel workbook
and directly work on Part 6.
And since it is a fresh copy, Excel don't show me the "can't ungroup" problem. Hope this help.
Thank you Yogesh for this wonderful tutorial.
Kent, Malaysia
Just when i thought pivots were awesome i learn about inserting the calculated fields and that makes them more awesome. chandoo where have you been all my life.
Hello - your P&L pivot version has really impressed my boss and would like to use it. I have applied it for a actual vs budget vs forecast model I have created. One problem. In your variance above the operating profit percent % variance shows 33.8% but I want it to show (0.01) point or the true diff from prior budget.
I know I can add calculation to the side but boss would like to see it in pivot table.
Please help
Thanks
I have a further query which may solve my above dilemma. Is it possible to add a column that calculates percent increase. So in the example above a new column would be added to show variance %.
Any help would be appreciated.
Thanks