In this article, I will show you two ways to create a financial planning calculator in Excel. Most of us need to determine the monthly payment or the amount of money to be saved for retirement over our lifetime. It is not easy to do such kinds of calculations manually. Fortunately, Excel Solver can also be applied in similar situations. As with other articles, I suggest you read my previous post, – Using Excel Solver to Determine the Optimal Product Mixtures – if you are not familiar with Excel Solver.
1. Creating Mortgage Financial Planning Calculator in Excel
Suppose that you are borrowing $15,000 to buy a new car and plan to make 10 end-of-month payments. The annual interest rate on the loan is 10 percent. The car dealer allows you to make the monthly payment for months 6-10 equal to half the payments for months 1–5. Assume that your payment occurs at the end of each month. Then, what is the payment for each month?
The snapshot below shows how to set the model for this problem. The beginning balance was put in column C and cell C5 equal to $15,000 which is the total amount of money that you borrowed. Monthly payment is what you seek, and D5 is our target cell. The interesting thing is that monthly payment is also a changing cell, and thus I left it blank at the moment.
As for column E, we should fill it with the monthly interest rate instead of the annual interest rate. Therefore, I had to divide the annual interest rate by 12 to get the monthly interest rate (10%/12 = 0.00833). Because the payment occurs at the end of each month, I put the formula “=C5*E5” in cell F5 to compute the interest incurred on the balance at the beginning of the first month. By copying this formula into cells F6 through F14, the interest that you have to pay for other months can be calculated. The relationship between column C, column D, column E, column F, and column G can be described by the following equation:
(Unpaid Loan Balance at End) = (Unpaid Loan Balance at Beginning) – [(Monthly Payment) – (Monthly Interest Paid)]
The formula “=C5-D5+F5” which is derived from this equation, was put in cell G5 to compute the ending balance in the first month. Copy this formula into range G6:G14 to calculate the ending balance for other months. We all know that the ending balance of this month will be the balance at the beginning of next month. Therefore, the formula “=G5” was entered into cell C6 and copied into range C6:C14 to display the beginning balance for months 3 through 10. Up until this point, I have described the process; now let’s go through this step-by-step.
- Firstly, type the necessary fields in the mortgage financial planning calculator. As I previously stated, the car dealer allows you to pay half from the sixth month onwards:
- Payment for the first six months will be equal to $D$5.
- The payment from month 6 will be $D$5/2.
- Secondly, type the following formula in cell C6.
- Next, type another formula in cell F5.
- Then, fill in the formula for the rest of the cells.
- Lastly, type this formula in cell G5 and fill it.
- This is what the steps will look like. Now, you need to implement Solver to find the payment as per the condition.
- Let’s see how to enable the Solver add-in.
- Press ALT, F, then T to bring up the Excel Options dialog box.
- Then, from the Add-ins tab, select “Go…”.
- Next, select Solver Add-in and press OK.
- From the Data tab, select Solver.
- The Solver Parameter dialog box for this model is shown below. The constraint for this model is that the ending balance for month 10 should be $0.
After clicking Solve in the Solver Parameters dialog box, you can find that your monthly payment for the first 5 months is $2,078.40. And during the other 5 months, you have to pay $1,039.20.
Read More: Some Practical Examples with Excel Solver
- How to Use Solver in Excel (with Detailed Steps)
- Where Is Solver in Excel?
- How to Do Portfolio Optimization Using Excel Solver
2. Creating Retirement Financial Planning Calculator in Excel
Suppose that you will deposit some money into your retirement account at the beginning of this year and each of the next 39 years. Each year, you plan to increase your retirement contribution by $500. When you retire 40 years later, you plan to withdraw $100,000 at the beginning of each year for 20 years. Assuming that the investments will earn 10 percent per year during the first 20 years and 5 percent per year during all other years, what is the least amount of money you can deposit this year and still have enough to make your retirement withdrawal?
To set up the model, I made a worksheet like that. The amount of money that you need to contribute to your retirement account each year will be entered into column D. And the amount of money that you withdraw each year will be entered into column F. Column E gives you the return on investment each year. Column C and column G tell you how much money is in your account at the beginning or the end of each year, respectively.
The relationship between these columns can be described by the following equation:
(Initial Balance + Deposit)*Return Percent – Withdrawal = Ending Balance
And the formula “=(C5+D5-F5)*(1+E5)” which is derived from this equation was put in cell G5 to compute the ending balance in the first year. By copying this formula from G5 into range G6:G64, we can get the ending balance for other years. The initial balance of this year should equal the ending balance of last year. Therefore, I put the formula “=G5” in cell C6 to get an initial balance for the second year. This formula was copied into cells C7 through C64 to get the initial balance for other years.
From the problem, we can know that cell D5 is our target cell, as what we need to know is exactly how much we need to save in the first year, and thus I will leave it blank at the moment. Interestingly, cell D5 is also our changing cell. Please note that the target cell and changing cell are the same in our case. Since you need to increase your investment by $500 each year, the formula “=D5+500” was entered into cell D6 to calculate how much you need to deposit in the second year. Then I copied this formula from cell D6 to D7:D44 to make sure that the retirement contribution increases by $500 per year during Years 3 through 40.
- To begin with, type all the required information from the statement above.
- Then, in cell C6 type the following formula and fill it into the rest of the cells.
- Next, type another formula in cell D6 and fill the formula up to cell D44.
- Afterward, type another formula in cell G5 and fill the formula in the rest of the cells.
- This is what the current step will look like.
- Now, following the previous method, bring up the Solver window,
The Solver Parameters for this model is shown below. The constraint for this model is “$G$45:$G$64>=0” because you need to make sure that the ending balance for years 41 through 60 is nonnegative. This model is linear because the constraint can be described in a linear relationship.
After clicking Solve in the Solver Parameters dialog box, you can find that you should save at least $1,569.41 into your account in the first year.
Read More: Example with Excel Solver to Minimize Cost
Other Financial Plannings
Additionally, you can use the built-in Office templates to do your financial planning calculator in Excel.
- Simply go to File, then select New.
- After that, search for your desired keyword.
- Then, download and edit that template as per your needs.
- Here is a sample Business Financial Plan template to work with.
Download Practice Workbook
I have shown you two methods to create a financial planning calculator in Excel. If you face any problems, feel free to comment below.