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

**equal to $15,000 which is the total amount of money that you borrowed. Monthly payment is what you seek, and**

*C5***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.**

*D5*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 “

**=**” in cell

*C5*E5***to compute the interest incurred on the balance at the beginning of the first month. By copying this formula into cells**

*F5***through**

*F6***, the interest that you have to pay for other months can be calculated. The relationship between column**

*F14***, column**

*C***, column**

*D***, column**

*E***, and column**

*F***can be described by the following equation:**

*G*`(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

**to compute the ending balance in the first month. Copy this formula into range**

*G5***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 “**

*G6:G14***=**” was entered into cell

*G5***and copied into range**

*C6***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.**

*C6:C14***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*

- Payment for the first six months will be equal to
- 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`

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

`=C5-D5+F5`

- This is what the steps will look like. Now, you need to implement
to find the payment as per the condition.*Solver*

- Let’s see how to enable the
add-in.*Solver* - Press
,*ALT*, then*F*to bring up the*T*dialog box.*Excel Options* - Then, from the
tab, select “*Add-ins*”.*Go…*

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

- From the
tab, select*Data*.*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**

**Similar Readings**

**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

**. Column**

*F***gives you the return on investment each year. Column**

*E***and column**

*C***tell you how much money is in your account at the beginning or the end of each year, respectively.**

*G*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

**into range**

*G5***, 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 “**

*G6:G64***=G5**” in cell

**to get an initial balance for the second year. This formula was copied into cells**

*C6***through**

*C7***to get the initial balance for other years.**

*C64*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

**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***=D5+500**” was entered into cell

**to calculate how much you need to deposit in the second year. Then I copied this formula from cell**

*D6***to**

*D6***to make sure that the retirement contribution increases by $500 per year during Years 3 through 40.**

*D7:D44***Steps:**

- To begin with, type all the required information from the statement above.
- Then, in cell
type the following formula and fill it into the rest of the cells.*C6*

`=$G5`

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

`=D5+500`

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

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

- This is what the current step will look like.

- Now, following the previous method, bring up the
window,*Solver*

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
, then select*File*.*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.

**Read More:** **Resource Allocation in Excel (Create with Quick Steps)**

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

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.