Functions for Personal Finance

The Fundamentals of Loans and Leases

In this section, we continue to develop the Personal Budget workbook. Notable items missing from the Budget Detail worksheet are the payments you might make for a car or a home. In addition, you may want to set and track a savings goal. We demonstrate Excel functions used to calculate lease payments for a car, calculate mortgage payments for a house, and project future savings based on regular contributions and an average rate of return. We also discuss the scenario capabilities of Excel once the Personal Budget workbook is complete.

Here, we add the PMT function to the Personal Budget workbook. This function calculates the payments required for a loan or a lease. However, before demonstrating this function, it is important to cover a few fundamental concepts on loans and leases.

A loan is a contractual agreement where money is borrowed from a lender and paid back over a specific period. The money borrowed from the lender is the principal of the loan. The borrower is usually required to pay the loan's principal plus interest. We call the loan a mortgage when you borrow money to buy a house. This is because the purchased home also serves as collateral to ensure payment. In other words, the bank can take possession of your house if you fail to make loan payments. (see Table 1)

Table 1 Key Terms for Loans and Leases

Term	Definition
Collateral	Any item of value that is used to secure a loan to ensure payments to the lender
Down Payment	The amount of cash paid toward the purchase of a house. If you are paying 20% down, you are paying 20% of the house's cost in cash and borrowing the rest from a lender.
Interest Rate	The interest that is charged to the borrower as a cost for borrowing money
Mortgage	A loan where the property is put up for collateral
Principal	The amount of money that has been borrowed
Residual Value	The estimated selling price of a vehicle at a future point in time
Terms	The amount of time you have to repay a loan

Figure 1 shows an example of an amortization table for a loan. A lender is required by law to provide borrowers with an amortization table when a loan contract is offered. The table in the figure shows how the loan payments would work if you borrowed $100,000 from a lender and agreed to pay it back over 10 years at an interest rate of 5%. Notice that each time you make a payment, you pay the bank an interest fee plus some of the loan principal.

Each year, the interest paid to the bank decreases, and the money used to pay off the principal increases. This is because the bank charges you interest on the amount of principal that has not been paid. As you pay off the principal, the interest rate is applied to a lower number, which reduces your interest charges. Finally, the figure shows that the sum of the values in the Interest Payment column is $29,505. This is how much it costs to borrow this money over 10 years. Indeed, borrowing money is not free. It is important to note that the payments were calculated annually to simplify this example. Most loan payments are made every month.

Figure 1 Example of an Amortization Table

A lease is a contract where you (the lessee) use an asset, such as a car or equipment, and you agree to make regular payments to the owner or the lessor. When you lease a car, the manufacturer or a leasing company retains ownership of the vehicle, and you agree to make regular payments for a specific period. The amount of money you pay depends on the car's price, the lease contract terms, and the car’s expected residual value at the end of the lease. The calculation of lease payments is similar to the calculation of loan payments.

However, when you lease a car, you pay only the value of the car. For example, suppose you are leasing a car priced at $25,000. The lease contract is for four years at an interest rate of 5%. The residual value of the car is $10,000. This means the car will lose $15,000 of its value over four years. Another way to state this is that the car will depreciate $15,000. A lease will be structured so that you pay this $15,000 in depreciation. However, the interest charges will be based on the purchase price of $25,000. In the next section, we will look at a demonstration of leasing a car and buying a home.

---

 This text was adapted by Saylor Academy under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License without attribution as requested by the work's original creator or licensor.

The PMT (Payment) Function for Loans

If you own a home, your mortgage payments are a major component of your household budget. Understanding your monthly payments is critical for maintaining strong financial health if you plan to buy a house. In Excel, mortgage payments are conveniently calculated through the PMT (payment) function.

With statistical functions, you must add only a range of cells or selected cells within the function's parentheses. With the PMT function, you must accurately define a series of arguments for the function to produce a reliable output. Table 2 lists the arguments for the PMT function. It is helpful to review the key loan and lease terms in Table 1 before reviewing the PMT function arguments.

Table 2 Arguments for the PMT Function

Argument	Definition
Rate	This is the interest rate the lender charges the borrower. The interest rate is usually quoted annually, so you must divide it by 12 to calculate monthly payments.
Nper	The argument letters stand for the number of periods. This is the loan term when you must repay the bank. This is usually quoted in years, so you must multiply the years by 12 if calculating monthly payments.
Pv	The argument letters stand for present value, which is the principal of the loan or the amount of money borrowed. A minus sign must precede the cell location or value when defining this argument. For leases, this argument is used for the price of the item being leased.
[Fv]	The argument letters stand for future value. The brackets around the argument indicate that it is not always necessary to define it. It is used if a lump-sum payment will be made at the end of the loan terms. This is also used for the residual value of a lease. If it is not defined, Excel will assume that it is zero.
[Type]	This argument can be defined with either a 1 or a 0. The number 1 is used if payments are made at the beginning of each period. A 0 is used if payments are made at the end of each period. The argument is in brackets because it does not have to be defined if payments are made at the end of each period. Excel assumes that this argument is 0 if it is not defined.

We will use the PMT function in the Personal Budget workbook to calculate the monthly mortgage payments for a house. These calculations will be made in the Mortgage Payments worksheet and then displayed in the Budget Summary worksheet through a cell reference link. So far, we have demonstrated several methods for adding functions to a worksheet. The following steps explain a new method using the Insert Function command for adding the PMT function:

1. Click the Mortgage Payments worksheet tab.
   
   
2. Click cell B5.
   
   
3. Click the Formulas tab on the Ribbon.
   
   

4. Click the Insert Function button (see Figure 2 Mortgage Payments Worksheet). This opens the Insert Function dialog box, which allows you to search for all Excel functions.
   
   

   
   

   
   

   Figure 2 Mortgage Payments Worksheet

5. In the "Search for a function:" input box at the top of the Insert Function dialog box, type mortgage payments (see Figure 3). The current description in the "Search for a function:" input box will already be highlighted. You can begin typing, and your entry will replace the description.
   
   
6. Click the Go button in the upper right side of the Insert Function dialog box. This adds all the Excel functions that match your description in the "Select a function:" box in the lower half of the Insert Function dialog box (see Figure 3).
   
   
7. Click the PMT option in the "Select a function:" box in the lower half of the Insert Function dialog box.
   
   

8. Click the OK button at the lower right side of the Insert Function dialog box. This will open the Function Arguments dialog box.

    

   Figure 3 Insert Function Dialog Box

   Mouseless Commands

   Insert Function: Hold the SHIFT key while pressing the F3 key.

9. Click the Collapse Dialog button next to the Rate argument in the Function Arguments dialog box. This will be the first argument defined for the function.
   
   
10. Click cell B3 on the worksheet. This is the rate being charged on the loan.
    
    
11. Type a forward slash (/) for division.
    
    
12. Type the number 12. Since our goal is to calculate the monthly payments for the loan, we need to divide the rate, which is stated in annual terms, by 12. This converts the annual rate to a monthly rate.
    
    
13. Press the ENTER key on your keyboard. This returns the Function Arguments dialog box to its expanded form. You will also see that the Rate argument is now defined.
    
    
14. Click the Collapse Dialog button next to the Nper argument in the Function Arguments dialog box. This is the second argument we define in the function.
    
    
15. Click cell B4 on the worksheet. This is the term or the amount of time we have to repay the loan.
16. Type an asterisk (*) for multiplication.
    
    
17. Type the number 12. Since our goal is to calculate the monthly payments for the loan, we need to multiply the loan terms by 12. This converts the terms of the loan from years to months.
    
    
18. Press the ENTER key on your keyboard. This returns the Function Arguments dialog box to its expanded form. You will also see that the Nper argument is now defined.
    
    
19. Click the Collapse Dialog button next to the Pv argument in the Function Arguments dialog box. This is the third argument we will define in the function.
    
    
20. Type a minus sign (−). When defining the Pv argument of the PMT function, any cell location or value must be preceded with a minus sign.
    
    
21. Click cell B2 on the worksheet. This is the principal of the loan.
    
    
22. Press the ENTER key on your keyboard. You will now see the Rate, Nper, and Pv arguments defined for the function.
    
    
23. Click the OK button at the bottom of the Function Arguments dialog box. The function will now be placed into the worksheet. Since we are not paying any lump sums at the end of the loan, there is no need to define the Fv argument. Also, we assume that monthly mortgage payments will be made at the end of each month. Therefore, there is no need to define the Type argument.

Figure 4 shows the completed Function Arguments dialog box for the PMT function. Notice that the dialog box shows the values for the Rate and Nper arguments. The Rate is divided by 12 to convert the annual interest rate to a monthly interest rate. The Nper argument is multiplied by 12 to convert the loan terms from years to months. Finally, the dialog box provides you with a definition for each argument. The definition appears when you click in the input box for the argument.

Figure 4 Function Arguments Dialog Box for the PMT Function

Figure 5 shows the final appearance of the Mortgage Payments worksheet after the PMT function is added. The function's result in cell B5 will be displayed in the Budget Summary worksheet.

Figure 5 Mortgage Payments Worksheet with the PMT Function

The PMT (Payment) Function for Leases

In addition to calculating the mortgage payments for a home, we use the PMT function in the Personal Budget workbook to calculate the lease payments for a car. The lease payment details are in the Car Lease Payments worksheet. Similar to the statistical functions, we can type the PMT function directly into a cell. However, you must know the definitions for each function argument and understand how to define these arguments based on your objective. The terms for loans and leases are in Table 1, and the definitions for the arguments of the PMT function are in Table 2.

The following steps explain how to add the PMT function to the Personal Budget workbook to calculate the car's lease payments:

1. Click cell B6 in the Car Lease Payments worksheet.
   
   
2. Type an equal sign: =
   
   
3. Type the letters PMT
   
   
4. Type an open parenthesis: (   Excel then provides a tip box showing the function's arguments.
   
   
5. Click cell B4. This is the interest rate being charged for the lease.
   
   
6. Type the forward slash for division: /
   
   
7. Type the number 12. Since we aim to calculate the monthly lease payments, we divide the interest rate by 12 to convert the annual rate to a monthly rate.
   
   
8. Type a comma. When you type a function containing arguments, you must separate each argument with a comma. This signals to Excel that one argument has been defined, and you are ready to define the next argument in the function.
   
   
9. Click cell B5. This is the term or the length of time for the lease contract. Since the term has already been expressed in months, we can reference cell B5 and move to the next argument.
   
   
10. Type a comma. This advances the function to the Pv argument.
    
    
11. Type a minus sign: −   Remember that cell locations or values used to define the Pv argument must be preceded by a minus sign.
    
    
12. Click cell B2 on the worksheet, which is the price of the car.
    
    
13. Type a comma. This advances the function of the [Fv] argument.
    
    
14. Click cell B3 on the worksheet. This is the residual value of the car. Note that cell location and values used to define the [Fv] argument are NOT preceded by a minus sign.
    
    
15. Type a comma. This advances the function to the [Type] argument.
    
    
16. Type the number 1. We assume the lease payments will be due at the beginning of each month.
    
    
17. Type a closing parenthesis: )
    
    
18. Press the ENTER key.

Figure 6 shows how the PMT function should appear before pressing the ENTER key. Notice the commas that separate each argument of the function. Also, the tip box will show the current argument being defined in bold font.

Figure 6 PMT Function Constructed to Calculate Lease Payments

Figure 7 shows the PMT function results. The monthly payment for this lease is $206.56, which will be displayed in the Budget Summary worksheet.

Figure 7 Results of the PMT Function in the Car Lease Payments Worksheet

Linking Worksheets (Creating a Summary Worksheet)

So far, we have used cell references in formulas and functions, which allow Excel to produce new outputs when the values in the cell references are changed. Cell references can also be used to display values or the outputs of formulas and functions in cell locations on other worksheets. This is how data will be displayed on the Budget Summary worksheet in the Personal Budget workbook. Outputs from the formulas and functions entered into the Budget Detail, Mortgage Payments, and Car Lease Payments worksheets will be displayed on the Budget Summary worksheet using cell references.

The following steps explain how this is accomplished:

1. Click cell C3 in the Budget Summary worksheet.
   
   
2. Type an equal sign: =
   
   
3. Click the Budget Detail worksheet tab.
   
   
4. Click cell D12 on the Budget Detail worksheet.
   
   
5. Press the ENTER key on your keyboard. The output of the SUM function in cell D12 on the Budget Detail worksheet will be displayed in cell C3 on the Budget Summary worksheet.

Figure 8 shows how the cell reference appears in the Budget Summary worksheet. Notice that the cell reference D12 is preceded by the Budget Detail worksheet name enclosed in apostrophes followed by an exclamation point ('Budget Detail'!) This indicates that the value displayed in the cell references a cell location in the Budget Detail worksheet.

Figure 8 Cell Reference Showing the Total Expenses in the Budget Summary Worksheet

As shown in Figure 8, the Budget Summary worksheet is designed to show the expense budget for the mortgage payments and the auto lease payments. However, you will recall that we used the PMT function to calculate the monthly payments. In the Budget Summary worksheet, we need to show the total annual payments. As a result, we will create a formula that references cell locations in the Mortgage Payments and Car Lease Payments worksheets.

The following steps explain how this is accomplished:

1. Click cell C4 in the Budget Summary worksheet.
   
   
2. Type an equal sign: =
   
   
3. Click the Mortgage Payments worksheet tab.
   
   
4. Click cell B5 in the Mortgage Payments worksheet.
   
   
5. Type an asterisk (*) for multiplication.
   
   
6. Type the number 12. This multiplies the monthly payments by 12 to calculate the total payments required for the year.
   
   
7. Press the ENTER key on your keyboard. The value of multiplying the monthly mortgage payments by 12 is now displayed on the Budget Summary worksheet.
   
   
8. Click cell C5 on the Budget Summary worksheet.
   
   
9. Type an equal sign: =
   
   
10. Click the Car Lease Payments worksheet tab.
    
    
11. Click cell B6 in the Car Lease Payments worksheet.
    
    
12. Type an asterisk (*) for multiplication.
    
    
13. Type the number 12. This multiplies the monthly lease payments by 12 to calculate the total payments required for the year.
    
    
14. Press the ENTER key on your keyboard. The value of multiplying the monthly lease payments by 12 is now displayed on the Budget Summary worksheet.

Figure 9 shows the results of creating formulas referencing cell locations in the Mortgage Payments and Car Lease Payments worksheets.

Figure 9 Formulas Referencing Cells in Mortgage Payments and Car Lease Payments Worksheets

We can now add other formulas and functions to the Budget Summary worksheet to calculate the difference between the total spend dollars vs. the total net income in cell D2. The following steps explain how this is accomplished:

1. Click cell D6 in the Budget Summary worksheet.
   
   
2. Type an equal sign: =
   
   
3. Type the function name SUM followed by an open parenthesis: (
   
   
4. Highlight the range C3:C5.
   
   
5. Type a closing parenthesis: ) and press the ENTER key on your keyboard. The total for all annual expenses now appears on the worksheet.
   
   
6. Click cell D7 on the Budget Summary worksheet.
   
   
7. Type an equal sign: =
   
   
8. Click cell D2.
   
   
9. Type a minus sign (−) and then click cell D6.
   
   
10. Press the ENTER key on your keyboard. This formula produces an output of $1,942, indicating that our income exceeds our total expenses.

Figure 10 shows the results of the formulas added to the Budget Summary worksheet. The output for the formula in cell D7 shows that the net income exceeds total planned expenses by $1,942. Overall, having your income exceed your total expenses is good because it allows you to save money for future spending needs or unexpected events.

Figure 10 Formulas Added to Show Income Is Greater Than Expenses

We can now add a few formulas that calculate both the spending and savings rates as a percentage of net income. These formulas require absolute references, which we covered earlier in this chapter. The following steps explain how to add these formulas:

1. Click cell E6 in the Budget Summary worksheet.
   
   
2. Type an equal sign: =
   
   
3. Click cell D6.
   
   
4. Type a forward slash ( / ) for division and then click D2.
   
   
5. Press the F4 key on your keyboard. This adds an absolute reference to cell D2.
   
   
6. Press the ENTER key. The formula results show that total expenses consume 94.1% of our net income.
   
   
7. Click cell E6.
   
   
8. Place the mouse pointer over the Auto Fill Handle.
   
   
9. When the mouse pointer turns to a black plus sign, left-click and drag to cell E7. This copies and pastes the formula into cell E7.

Figure 11 shows the output of the formulas calculating the spending rate and savings rate as a percentage of net income. The absolute reference shown for cell D2 prevents the cell from changing when the formula is copied from cell E6 and pasted into cell E7. The formula results show that our current budget allows for a savings rate of 5.9%. This is a fairly good savings rate. In the next section, we will discuss how these savings can grow over time by exploring the concepts of the time value of money.

Figure 11 Calculating the Savings Rate

Time Value of Money Concepts

In reviewing the Budget Summary worksheet in Figure 11, you will notice that the range B9:D14 contains data that can be used to assess a savings plan. We can project how much money can be saved over a specific period given set contributions and a rate of return. This calculation is accomplished through the future value, or FV, function. We will use the FV function in cell D10 of the Budget Summary worksheet to calculate our savings plan projection. However, before we use the FV function, it is important to review a few basic concepts regarding the time value of money, as shown in Table 3.

Table 3 Key Terms for Time Value of Money Concepts

Argument	Definition
Annuity	An investment that is made in regular payments over some time. For example, depositing $100 monthly into an interest-bearing bank account or mutual fund is considered an annuity.
Bonds	An investment in which you lend money to a company or government entity. The borrower agrees to pay you interest over a specific period. At the end of the bond agreement, the amount borrowed or your initial investment is returned to you. Most bonds are considered lower-risk investments but offer lower returns than stocks.
Mutual Funds	A collection of similar investments managed by a financial professional (a fund manager). Mutual funds allow you to invest in several stocks or bonds without making many individual investments. They also allow you to reduce your risk and take advantage of the investment expertise of a professional.
Rate of Return	The percentage gained or lost on an investment. Investments that offer a high predicted rate of return often carry a higher risk of losing money. Investments that offer a lower predicted rate of return often carry a lower risk of losing money.
Stocks	An investment in which you own a portion of a company. The value of this investment increases as the company produces higher profits. Most stocks are expected to generate a higher rate of return than bonds generate. However, the risk of losing money on a stock investment is much greater than bond risk.

Table 3 provides definitions for several terms used when addressing the time value of money concepts. The time value of money is the opportunity to grow your money over time, given a constant or average rate of return. For example, consider the data shown in Figure 12. This data assumes that a person makes a one-time investment of $100 in a bond mutual fund that returns five percent interest annually. Notice that the interest paid in Column E increases every year. This is because the interest is reinvested in the mutual fund, which increases the total value of the investment.

For example, the interest earned in year one is based on a $100 investment. Therefore, the interest paid is $5.00, or five percent\ of $100. However, in year \two, when the $5.00 interest payment is reinvested, the total investment increases to $105. Therefore, in year two the interest paid increases to $5.25, or five percent of $105. The value of the investment at the end of five years is $127.63. This is the value that can be calculated using the FV function.

Figure 12 Time Value of Money Example for a One-Time Investment

Figure 13 shows another example demonstrating the time value of money concept. Instead of making a one-time investment, we will assume that a person invests $100 at the beginning of every year in the same bond mutual fund. This is called an annuity because the person makes reoccurring investments over a specific period. Notice that the value of this investment after five years is $580.19. Also, the total interest earned on this investment is $80.19 as opposed to the $27.63 earned on the one-time investment in Figure 12.

Figure 13 Time Value of Money Example for an Annuity Investment

The FV (Future Value) Function

Establishing a personal savings plan is one of the most important financial exercises. For example, a savings plan is critical for establishing financial security for retirement. Many people mistakenly believe that saving for retirement is something you do when you get older. However, the greatest financial gains for your retirement can be achieved if you start saving in the earliest years of your career.

Now that you understand the time value of money, you can see that the more years you can earn interest on your investments and reinvest those earnings, the more money you will have when you retire. Savings plans are also important for other key life events, such as attending college or buying a home. The FV function is a convenient tool that can help you establish savings goals and project the value of your investments over time. Like the PMT function, the FV function requires you to accurately define specific arguments to produce a reliable result. Table 4 provides definitions for each of the arguments in the FV function. Reviewing the time value of money terms in Table 3 before using the FV function is helpful.

Table 4 Arguments for the FV Function

Argument	Definition
Rate	This is the rate of return you expect to earn on an investment over time. This rate is usually quoted in annual terms, so you have to divide it by 12 if you are calculating the value of an annuity and making investments monthly.
Nper	The argument letters stand for the number of periods. This is the amount of time you are using to measure the value of an investment. The time used to define this argument must be comparable to the Rate argument. For example, if the rate is stated in terms of months, the amount of time used to define this argument must be in months.
Pmt	The argument letters stand for payment. This argument is used if you are measuring the value of an annuity investment. The argument is defined with the value of the investment made for each measure of time used to define the Nper argument. For example, if the Nper argument is expressed in terms of months, you must define this argument with the investment value made every month.
[Pv]	The argument letters stand for present value. The brackets around the argument indicate that it is not always necessary to define it. Excel assumes zero if the argument is not defined. The argument is used when measuring the value of a one-time investment. This argument and the Pmt argument will be defined if an annuity investment has a beginning balance or includes a beginning one-time lump-sum investment.
[Type]	This argument can be defined with either a 1 or a 0. The number 1 is used if investments are made at the beginning of each period to define the Nper argument. A 0 is used if the investments are made at the end of each period. The argument is in brackets because it does not have to be defined if your investments are made at the end of each period. Excel assumes that this argument is 0 if it is not defined.

For the Personal Budget workbook, we will use the FV function to project the value of the savings plan in 10 years. We will type the function directly into the Personal Budget worksheet for this demonstration. However, you can use any of the methods demonstrated in this chapter for future use. The following steps explain how this function is added to the worksheet:

1. Click cell D10 in the Budget Summary worksheet.
   
   
2. Type an equal sign: =
   
   
3. Type the letters FV followed by an open parenthesis: (
   
   
4. Click cell D13. This is the expected rate of return for the investments.
   
   
5. Type a comma.
   
   
6. Click cell D12. This is the amount of time the investments are expected to grow.
   
   
7. Type a comma.
   
   
8. Type a minus sign (−). All values or cell locations used to define the Pmt argument must be preceded by a minus sign.
   
   
9. Click cell D7. This change in cash was calculated by subtracting the total expenses from the net income. We expect to save this money for the 10-year period this investment is being measured.
   
   
10. Type a comma.
    
    
11. Type a minus sign (−). All values and cell locations used to define the Pv argument must be preceded by a minus sign.
    
    
12. Click cell D14. Since the savings plan has a current balance, we use this to define the function's Pv argument. This is equivalent to starting with a lump-sum investment.
    
    
13. Type a closing parenthesis ( ) ). There is no need to define the last argument of the function because we will assume that the savings in cash achieved in our budget will be invested at the end of each year of the savings plan.
    
    
14. Press the ENTER key. Check that cell D11 is activated.
    
    
15. Type an equal sign: =
    
    
16. Click cell D10.
    
    
17. Type a minus sign (−) and then click cell D9. This subtracts the savings plan from the current savings plan projection.
    
    
18. Press the ENTER key.

Figure 14 shows the results of the FV function. Notice that the current savings plan projection is $25,606. This is $606 higher than the target of $25,000 entered into cell D9, which shows that the current budget is working to achieve the goals of this savings plan. In other words, given the current net income, we are saving enough money to achieve our savings plan goals.

There are two important factors to notice about this plan. The first factor is that our spending plan allows us to save enough money to be invested to achieve our target of $25,000. The second factor is that the expected rate of return is 3.5%. This is a relatively low expected rate of return and could be achieved by investing in relatively low-risk investments such as bonds as opposed to stocks. This rate can be considered good because we can achieve our savings goals without making high-risk investments that could significantly lose our savings.

Figure 14 Results of the Savings Plan Projections

Goal Seek (What-If Scenarios)

We used several formulas and functions to complete the Personal Budget workbook shown in Figure 14. All the formulas and functions entered contain cell references that allow for various what-if scenarios. You can use Goal Seek to conduct these what-if scenarios. Goal Seek maximizes the benefits of Excel’s cell-referencing capabilities by changing inputs to precise values to achieve specific outputs produced by formulas or functions.

We begin by changing one of the inputs in the Personal Budget workbook through the following steps:

1. Click the Budget Detail worksheet tab.
   
   
2. Click cell D9.
   
   
3. Type the number 2000. Instead of planning a decrease in our vacation spending, we see what happens to our budget if we spend the same amount as last year, which was $2,000.
   
   
4. Press the ENTER key.

Figure 15 and Figure 16 show the Budget Detail worksheet before and after the change in the annual vacation budget. Compare these two figures to see that many of the outputs, formulas, and functions produced in the worksheet changed by changing just one input.

Here is a list of the changes that occurred in the worksheet:

• The formula output in cell F12 shows that we are planning a 1.1% increase in our total spending instead of a −1.7% decrease.
  
  
• The formula output in cell F9 changes from −25% to 0%.
  
  
• The SUM function in cell D12 changes from $17,950 to $18,450.
  
  
• The SUM function in cell C12 changes from $1,496 to $1,538.
  
  
• The AVERAGE function in cell D14 changes from $1,994 to $2,050.
  
  

 

Figure 15 Budget Detail Worksheet before Changing the Annual Vacation Budget

Figure 16 Budget Detail Worksheet after Changing the Annual Vacation Budget

In addition to the changes in the Budget Detail worksheet, outputs of formulas and functions on the Budget Summary worksheet also changed when the Annual Spend for the Vacation category was increased. To see the changes, compare Figure 14 to Figure 17. There were 14 changes in the outputs of formulas and functions on the Budget Summary worksheet. In total, 21 outputs changed in the Personal Budget workbook due to changing just one input.

Figure 17 Budget Summary Worksheet after Changing the Annual Vacation Budget

One of the most notable changes in the Budget Summary worksheet is the Savings Projection in cell D10. Spending an additional $500 a year on vacation plans decreases the value of the projected savings in 10 years by $5,865. However, what if the rate of return were to increase? An increase in the rate of return could recover the decrease in the future value of our savings plan. We can use Goal Seek to determine how much the rate of return would increase to achieve our savings plan target of $25,000.

The following steps explain how to use Goal Seek to accomplish this goal:

1. Click the Budget Summary worksheet tab.
   
   
2. Click the Data tab of the Ribbon.
   
   
3. Click the What-If Analysis button in the Data Tools group of commands.
   
   

4. Click Goal Seek from the list options (see Figure 18 Selecting Goal Seek from the What-If Analysis Options). This opens the Goal Seek dialog box.

   Mouseless Commands

   Goal Seek: Press the Alt key on your keyboard and then the letters A, W, and G one at a time.

   
   

   Figure 18 Selecting Goal Seek from the What-If Analysis Options
   
   

5. Click the Collapse Dialog button next to the "Set cell:" input box on the Goal Seek dialog box.
   
   
6. Click cell D10 on the Budget Summary worksheet.
   
   
7. Press the ENTER key on your keyboard.
   
   
8. Place the mouse pointer over the "To value" input box in the Goal Seek dialog box and click.
   
   
9. Type 25000 in the "To value" input box in the Goal Seek dialog box.
   
   
10. Click the Collapse Dialog button next to the "By changing cell" input box in the Goal Seek dialog box.
    
    
11. Click cell D13 on the Budget Summary worksheet.
    
    
12. Press the ENTER key on your keyboard.
    
    
13. Click the OK button on the Goal Seek dialog box.
    
    
14. Click the OK button on the Goal Seek Status dialog box (see Figure 20). The status box tells you that Excel found a value for cell D13 that produces an output of $25,000 for the FV function in cell D10.
    
    
15. Figure 19 shows the final settings for the Goal Seek dialog box before clicking the OK button.
    
    

Figure 19 Final Settings for the Goal Seek Dialog Box

Figure 20 shows the solution Goal Seek calculated for the rate of return. Notice that to achieve the target savings plan of $25,000, the rate of return must increase to 7.8%. Initially, it appears that we can spend the additional $500 a year on vacations and still achieve our savings goal of $25,000. However, achieving a 7.8% annual rate of return will require us to make riskier investments with our savings.

Thus, we could lose a substantial amount of our savings. This is the downside of decreasing your overall savings rate. If you save less money, it forces you to take higher risks with the money you have to achieve higher rates of return. Unfortunately, many people end up losing out on these risks, which severely compromises their ability to reach their savings goals.

Figure 20 Solution Calculated by Goal Seek