How to Create Financial Planning Calculator in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

Creating Mortgage Financial Planning Calculator in Excel

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.

Steps:

  • 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.

=G5

  • Thirdly, using the Fill Handle, AutoFill the formula.

  • Next, type another formula in cell F5.

=C5*E5

Create Mortgage Financial Planning Calculator in Excel

  • Then, fill in the formula for the rest of the cells.
  • Lastly, type this formula in cell G5 and fill it.

=C5-D5+F5

  • 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…”.

Solver Add-in

  • Next, select Solver Add-in and press OK.

  • From the Data tab, select Solver.

Solver Tab

  • 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.

Solver Settings

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: Example with Excel Solver to Minimize Cost


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.

Creating Retirement Financial Planning Calculator in Excel

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.

Steps:

  • 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.

=$G5

  • Next, type another formula in cell D6 and fill the formula up to cell D44.

=D5+500

Creating Retirement Financial Planning

  • Afterward, type another formula in cell G5 and fill the formula in the rest of the cells.

=(C5+D5-F5)*(1+E5)

Ending Balance

  • 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 are 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.

Solver Settings 2

After clicking Solve in the Solver Parameters dialog box, you can find that you should save at least $1,569.41 in your account in the first year.

Final Output of Retirement Financial Planning Calculator in Excel

Read More: How to Use Excel Solver to Determine Which Projects Should Be Undertaken


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.

Other Financial Planning Calculator in Excel


Download Practice Workbook


Conclusion

I have shown you two methods to create a financial planning calculator in Excel. If you face any problems, feel free to comment below.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Zhiping Yan
Zhiping Yan

I am from China and this photo was taken in a classical garden. There are many similar gardens in China, attracting a lot of visitors every year, especially in spring and summer. I was major in Biotechnology. But I took a job as a SAS programmer because I prefer programming. Besides SAS, I also learned Excel VBA in my spare time. It is fantastic to be able to manipulate data, files and even to interact with the internet... Read Full Bio

2 Comments
  1. Was this first scenario taken from a 60 month example? The car dealer allows you to make the monthly payment for Month 31-60 equal to half the payments for Months 1 through 60.
    Months 31 to month 60! Number don’t make sense.

    • Hi, Mark
      Thanks for your query. This might be a 1-60 months problem. Moreover, the complete scenario is a presumption of the author. So, the loan payment conditions may not be similar to the day-to-day practice situation. If you have specific terms and conditions in your loan statement, you can send that to our problem-solving team. They will help you to fulfill your requirement.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo