Excel 2013 is a powerful tool that can help you perform various financial calculations. One of the most useful features of Excel is the “what-if” analysis, which allows you to see how changes in different variables can affect the outcome of a calculation. In this tutorial, we’ll focus on how to perform a goal seek analysis in Excel 2013.
To start, let’s say you want to determine what car you can afford based on your monthly budget. In this example, you can afford a monthly payment of $400, and you have a down payment of $3,000. You can finance the car for four years at an interest rate of 5.9%. To calculate the amount of the loan, you subtract the down payment from the price of the car.
To use the PMT function in Excel to determine the monthly payment, you enter “=PMT(B5/12,48,B2-B3)” (without quotes) into the formula bar. Here, B5 is the interest rate, divided by 12 to get the monthly rate. 48 is the number of months in the loan. B2-B3 is the amount of the loan.
Next, you can use the goal seek analysis to determine the price of the car you can afford based on your monthly budget. To do this, go to the “Data” tab in Excel and click on “What-If Analysis.” Then, select “Goal Seek.” Set cell F3 (the monthly payment) to $400 by changing cell B2 (the price of the car). Excel will calculate the price of the car you can afford based on your budget.
In addition to the car example, you can also use a goal seek analysis for retirement planning. In this scenario, you have a current savings balance of $50,000, an interest rate of 8%, and 35 years until retirement. You want to know how much you need to save each year to reach a retirement fund of $1.3 million.
To determine this, use the future value formula in Excel. Enter “=FV(B14, B13, 0, B11)” (without quotes) into the formula bar. Here, B14 is the interest rate, B13 is the annual payment, B11 is the number of years until retirement.
Next, use the goal seek analysis to determine the annual payment you need to make to reach your retirement fund goal. Set cell F15 (the future value) to $1.3 million by changing cell F13 (the annual payment). Excel will calculate the annual payment you need to make to reach your goal.
In conclusion, Excel 2013 is a powerful tool for financial analysis. The goal seek analysis is a useful feature that allows you to calculate different financial scenarios based on various variables. Whether you are buying a car or planning for retirement, Excel can help you make informed financial decisions.