Excel Module 9 Training
EX9748 Fill a series based on a growth trend.
Click and drag to select C5:H5. For this range, you want to fill in the values between the first and last cells. You will use a growth trend to do this. On the Home tab, in the Editing group, click the Fill button. The Fill menu opens. Click Series. The Series dialog box opens. In the Type section, click the Growth option button. Click the Trend check box. Excel will calculate the values between the starting value and the ending value based on a constant percentage of growth from year to year. Click OK.
EX9749 Fill a series based on a linear trend.
Click and drag to select cells C5:H5. For this range, you want to fill in the series assuming a constant amount of growth per year. On the Home tab, in the Editing group, click the Fill button. The Fill menu opens. Click Series. The Series dialog box opens. In the Type section, the Linear button is selected by default. You will accept this setting. In the Series dialog box, click the Trend check box. Click OK.
EX9829 Fill a series based on extrapolation.
Click and drag to select range B5:F5. You want to fill the blank cells in this range with a growth series using extrapolated, or projected, values. In the Editing group, click the Fill button. The Fill menu opens. Click Series.The Series dialog box opens. In the Series in section, the Rows option is currently selected. You will accept this setting; Excel will fill the selected row. In the Type section, click the Growth option button. You are extrapolating a growth trend rather than a linear trend, meaning the values will increase by a set percentage rather than a set amount.In the Step value box, select the text and then type 1.1 as the new step value. Excel will apply a 10 percent growth rate by multiplying each value in the extrapolated series by 1.1. Click OK.
EX8876 Create a formula using the PMT function.
Click cell B9. Type -PMT( in the current cell. Note that you are using a - (negative) sign before the PMT function so that the formula will return a positive value. Click cell B6. This is the rate argument, specifying the monthly interest rate. Excel displays the cell address, B6, in the formula. Type , (a comma) after the rate argument. Next, you will enter the nper argument, specifying the number of monthly payments. Click cell B8. Type , (a comma) after the nper argument. Next you will enter the pv argument, specifying the amount of the loan. Click cell B4. Click the Enter button on the formula bar. -PMT(B6,B8,B4)
EX9816 Create a formula using the NPER function.
Click cell B9. You will use the NPER function here to calculate the number of payments for a $500,000 Capital Improvements loan. Type =NPER(B6,B7,B4) in cell B9.As you type, Excel prompts you for function arguments. After typing each cell reference, Excel highlights that cell in the worksheet. Note that the pmt argument in cell B7 is a negative number, as loan payments are debits. Press ENTER. Excel then returns the result of the formula in cell B9
EX9839 Create a formula using the SLN function.
Click cell C11. You will use the SLN function here to calculate the straight-line depreciation for the company's office equipment. Type =SLN( in cell C11. As you type, Excel displays screen tips with information about the SLN function and prompts for function arguments. Click cell D3 to enter the cost argument. Press F4. F4 converts the cell reference to an absolute reference so you can accurately copy the formula to the remaining cells in the column. Type a , (comma) in cell C11 to continue the formula. Click cell D5 to enter the salvage argument. Press F4. Type a , (comma) in cell C11 to continue the formula. Click cell D4 to enter the life argument. Press F4. Type ) in cell C11 to finish the formula, then press ENTER.
EX9818 Create a formula using the RATE function.
Click cell C4. You will use the RATE function to calculate the interest rate required to reach a savings goal of $250,000, given an initial investment of $50,000 and deposits of $7,000 a month for 24 months. Type =RATE( in cell C4.As you type, Excel's AutoComplete feature prompts you for function arguments. Click cell C6The number of deposits, or payments (nper), is 24. Type a , (comma) after the cell reference.Click cell C7.The monthly payment amount (pmt) is $7,000. Note the number in cell C7 is a negative number because these deposits are a negative cash flow. Type a , (comma) after the cell reference.Click cell C3.The present value (PV) of the investment is the initial deposit of $50,000, again a negative cash flow. Type a , (comma) after the cell reference.Click cell C8. The investment goal (FV) is $250,000. Type ) to complete the formula.Press ENTER.
EX9629 Create a formula using the PV function.
Click cell C8. You will enter the PV function here to calculate the Loan Amount you could borrow using monthly input values from cells C5:C7. Type =PV( in cell C8. As you type, Excel displays screen tips with information about the PV function and prompts for function arguments. Click cell C6.The first argument you will enter is the interest rate. Because the loan will be paid monthly, you will use a monthly interest rate for this calculation. In cell C8, type a , (comma) to continue the formula.Function arguments must be separated by commas. You will now enter the number of loan payments. Click cell C7. You want a four-year loan with a total of 48 monthly payments. In cell C8, type a , (comma) to continue the formula. Click cell C5. Each monthly payment will be $8,000. In cell C8, type ) to complete the formula, and press ENTER.
EX9815 Create a formula using the IRR function.
Click cell D10. You want to calculate the internal rate of return for Scenario C in order to compare it to the other two scenarios. Type =IRR(D4:D9) in cell D10 and then press ENTER. The IRR function will calculate the internal rate of return based on the cash flow values in cells D4:D9. By not entering a value for the Guess argument, you accept the default value of 0.1. When you press ENTER, Excel performs the calculation and returns the result in cell D10.
EX9814 Trace formula precedents and dependents.
Click cell D8. Click the Formulas tab on the ribbon. In the Formula Auditing group, click the Trace Precedents button. Excel adds a blue tracer arrow pointing to cell D8 from the range D5:D7; it also adds a blue border to the range. The tracer arrow indicates that all of the cells within the border are precedents used to calculate the value in cell D8. In the Formula Auditing group, click the Trace Dependents button.
EX9817 Create a formula using the NPV function.
Click cell D9. Excel will calculate the value, in today's dollars, of an investment that pays a series of returns shown in range D5:D8 at a 10% rate of return. Type =NPV(D4,D5:D8) in cell D9, and press ENTER. In cell D9, type a formula using the NPV function to calculate the net present value of investment Scenario C using the rate of return in cell D4 and the values in range D5:D8 as the function arguments.
EX9822 Create a formula using the IPMT function.
Click cell F20. You will use the IPMT function here to calculate the interest portion of loan payment 6 using the parameters listed in the Loan Summary table. Type =-PPMT( in cell F20. Because loan payments are debits, you need to type a negative sign in front of the function name to return a positive value. As you type, Excel displays prompts for function arguments. Click cell D7.This is the monthly interest rate. Press F4 to use an absolute reference. F4 converts the cell reference to an absolute reference so you can accurately copy the formula to the remaining cells in the column. Type a , (comma) to continue the formula.Next, you will enter the per argument. Click cell A20. You are calculating the interest for loan payment number 6, referenced by cell A20. By using a relative cell reference here instead of typing the number 6, you can copy the formula to other column cells.Type a , (comma) to continue the formula. Next, you will enter the nper argument, or the total number of payments associated with the loan. Click cell D9. Press F4 to use an absolute reference. Type a , (comma) to continue the formula. You will now enter the loan amount. Click cell D5. Press F4 to use an absolute reference. Type ) to complete the formula. Press ENTER.
EX9833 Trace errors in a formula.
Click cell G10.You want to find the source of the error in the cell's formula. Click the Formulas tab on the ribbon. In the Formula Auditing group, click the Error Checking arrow. The Error Checking menu opens. Click Trace Error. Tracer arrows appear, revealing that the formula in cell G10 references cell D10, which contains a 0 (zero). Because the formula in G10 requires dividing by cell D10, and division by 0 cannot return a result, Excel marks it as an error. Click cell G13. Now you will trace the error in this formula. In the Formula Auditing group, click the Error Checking arrow. Click Trace Error.
EX9840 Create a formula using the DB function.
Click cell H12. You will use the DB function here to calculate the declining balance depreciation for the company's office equipment. Type =DB( in cell H12. As you type, Excel displays screen tips with information about the DB function and prompts for function arguments. Click cell D4 to enter the cost argument. Press F4. F4 converts the cell reference to an absolute reference so you can accurately copy the formula to the remaining cells in the column. Type a , (comma) to continue the formula. Click cell D6 to enter the salvage argument.Press F4. Type a , (comma) to continue the formula. Click cell D5 to enter the life argument.Press F4. Type a , (comma) to continue the formula. Click cell F12. You are calculating the depreciation for year 3 in the depreciation cycle. Type ) (a closed parenthesis) to finish the formula, and press ENTER.
EX9824 Create a formula using the CUMIPMT function.
Click cell H5. You will use the CUMIPMT function to calculate the cumulative interest paid during the third year of the loan. Type =-CUMIPMT( in cell H5. (Note the minus sign after the equal sign.) Because loan payments are treated as debits, you need to type a negative sign in front of the formula to return a positive number. Click cell D6. You will enter the rate argument as a monthly value because loan payments are made on a monthly basis. In cell H5, type a , (comma) to continue the formula. You will now enter the loan period (nper) argument in months. Click cell D8. Cell D8 contains the number of months in the loan period. In cell H5, type a , (comma) to continue the formula. You will now enter the loan amount (pv) argument.Click cell D4. You will finish the formula by typing entries for the start_period, end_period, and type arguments. Type ,25,36,0) in cell H5, and press ENTER.
EX9826 Use Error Checking.
Click the Formulas tab on the ribbon. In the Formula Auditing group, click the Error Checking button. The Error Checking dialog box opens. Excel identifies and describes an error found in cell G10, which is a Divide by Zero error. You do not want to make any changes to the cell, so you will proceed to the next error. Click Next. Excel identifies an error in the formula in cell G13. You will edit the cell reference in the formula. In the Error Checking dialog box, click the Edit in Formula Bar option. Excel activates the formula bar. In the formula bar, select the reference for cell C13 and type D13 in the formula bar. The formula now references a number rather than a text value. Press ENTER. Excel recalculates the formula using the new cell reference. In the Error Checking dialog box, click Resume. Excel resumes checking the worksheet for errors. A Microsoft Excel dialog box opens with the message that the error check is complete. In the Microsoft Excel dialog box, click OK
EX9628 Create a formula using the FV Function.
Excel's financial functions can save you the work of creating complicated formulas. Using the FV function in a formula helps you determine whether the amount you plan to invest or save will meet your financial goal, whether buying a car or planning for the future. Click cell C9. You will enter the FV function here to calculate the accumulated savings using inputs from cells C6:C8. Click the Formulas tab on the ribbon. Click the Insert Function Button. The Insert Function dialog box opens. Click the Or select a category arrow. Excel displays a list of function categories. Click Financial. Excel displays a list of financial functions in the Select a function box. Click FV. The FV function is selected. Excel displays a brief description of the function and its syntax. Click OK. The Function Arguments dialog box opens. The first argument you will enter is the interest rate.'Type C6 in the Rate text box. You will use the monthly rate in the formula to correspond to other inputs.'Press TAB.You will now enter the investment period, also in months. Type C7 in the Nper text boxPress TAB. You will now enter the amount of each monthly savings deposit, or investment. Type C8 in the Pmt text box. The monthly investment amount is a debit and is entered in the formula as a negative number. All required function arguments are now entered. You will leave the remaining optional arguments blank.Click OK.