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:
19 Responses to “How to Distribute Players Between Teams – Evenly”
An excellent solution, especially for large data sets.
Another solution without using solver would be to assign the player with the highest score to Team 1, the 2nd to team 2, 3rd to team 3, 4th to team 3, 5th to team 2, 6th to team 1, 7th to team 1 and it continues. This method would end up with a Std Dev of 0.001247219. This works best with a distribution with lower Std Dev for the dataset.
Full Disclosure: this is not my idea, remember reading something a few years ago. Think it may have been Ozgrid
thinking back I now remember why I read about it. About 10 years back I had to distribute around 300 team members into 25-30 odd teams. Used this method based on their performance scores. I used the method I described to do this and the distribution was pretty fair.
Solver would have saved me a ton of time though 🙂
I think the issue with you first Solver approach was that you took the absolute value of the sum of team deviations (which should always be zero except for rounding) instead of the sum of the absolute values (which is a reasonable measure of how unbalanced the teams are).
Here's another simple algorithm you could use: you start from the top (with players sorted from high to low), and at each step allocate the next player to whichever team has the smallest total so far. You can implement it dynamically with some formulas so it will update automatically when the data changes.
If the scores were more widely distributed (so that this might end up with not all teams the same size), you could add a constraint to only pick among the teams which currently have fewest players at each step, or just stop adding to any team when it hits its quota.
When I tried it on the sample, I got the three teams below, with a STDEV of 0.000942809 (i.e. about half of what Solver got to).
Team 1: John, Hugo, Tom, Josh, Eric, Zane, Charles, Andrew
Team 2: Barry, Michael, Kenny, Joe, Xavier, Patrick, Oliver, William
Team 3: Henry, Steven, Ben, Frank, Kyle, Edward, Cameron, Lachlan
Thanks for sharing!
Hi,
I was looking at all the solutions and this is closest to what I intended to do. I am dividing a bunch of players into 3 soccer teams. Players availability is also a factor while deciding the teams.
So the steps the excel needs to do is as follows:
1) In availability column if "yes" go to next
2) Equally divide 'Goalkeepers', 'Strikers', 'Defenders' basis their quality
So the end result gives each 3 teams a balance of players playing at different positions.
Can this be done on Google spreadsheet with only availability as an input from the user and rest calculates by itself.
Sorry for asking such a pointed question, but I have been struggling to find a solution for it for sometime now!
Hi Ishaan,
I am working on a similar problem at the moment, so I am wondering if you ever found a solution and if you are willing to share what you did.
Hi everyone, this is a variation of the famous Knapsack Problem https://en.wikipedia.org/wiki/Knapsack_problem.
I had to use a VBA implementation recently as part of a problem, where we ar trying to allocate teams of an organization into different locations (we are a large company with many different team). The goal was to optimally allocate teams to individual buildings without putting too many teams into one building and not splitting teams apart.
As we had around 400 teams of different sizes, solver couldn't handle it anymore. Luckily there is a Knapsack algorithm implementation in VBA readily available on the internet :).
I also went with a heuristic approach first!
An interesting mathematical solution but what if Eric and Xavier can't stand each other or Patrick is best friends with Steven - the real life problems that effect "even" teams.
@Joe
You can add more criteria like
If Eric and Xavier can't stand each other
=OR(AND(E15=1,E16=1),AND(F15=1,F16=1),AND(G15=1,G16=1))
It must be False
If Patrick is best friends with Steven
=OR(AND(E5=1,E17=1),AND(F5=1,F17=1),AND(G5=1,G17=1))
It must be True
Note that the 2 formulas above are exactly the same
except for the ranges
One must be True = Friends
One must be False = Not Friends
Nice Post!
Just one question What if number of players are not even or equally divisible.
Nice post Hui!
I download your workbook and just try to change in options the Precision Restriction from 10E-6 to 10-8 and the Convergence from 10E-4 to 10E-10. The process take almost the same time, but the results was great.
The standard deviation I got was 0,000471.
Team 1: John, Tom, Kenny, Frank, Eric, Xavier, Edward, Zane
Team 2: Steven, Hugo, Ben, Joe, Josh, Oliver, Cameron, William
Team 3: Barry, Henry, Michael, Kyle, Patrick, Charles, Andrew, Lachlan
Great application of Solver! Thanks for the link!
Great explanation. Well done... However, I tried with 6 teams of 4 players and solver never did finish.
How about vba code for the same data set.
I have 3 column A B C wherein A has text and B has number Wherein C is blank. And in C1 been the header C2 where I want the name to come evenly distributed the number which is in Column B.
My Lastcolumn is 1000.
Sorry if I'm being slow here, but how is 'Team Score' calculated? I've gone through the explanation several times but it seems to just appear.
@Hrmft
This process uses the Solver Excel addin
Solver is effectively taking the model and trying different solutions until it gets a solution that meets all the criteria
Then solver puts the solution into the cell and moves to the next cell
So yes it appears to "just appear"
Hi ! Thank you so much ! Works great 🙂
I cannot get the fourth Equation to work in my excel spreadsheet
You have =($E$2:$G$25=0)+($E$2:$G$25=1)=1 as a SUMIF solution, I have, =($F$2:$H$13=0)+($F$2:$H$13=1)=1 as my solution but it does not work. The only thing I changed is the ranges. Any suggestions?
Thank you.
Jim
I cannot get the fourth Equation of TURE or FALSE statements to work in my excel spreadsheet You have =($E$2:$G$25=0)+($E$2:$G$25=1)=1 as a SUMIF solution, I have, =($F$2:$H$13=0)+($F$2:$H$13=1)=1 as my solution but it does not work. The only thing I changed is the ranges. Any suggestions?
Sorry I left some of it out in the previous question,
Thank you. Jim