T Tu orial
9
O b j ectives
Session 9.1 Work with financial functions to analyze loans and investments Create an amortization schedule Calculate a conditional sum
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
Working with Financial Tools and Functions
Developing a Financial Analysis Case|Bent Cycling
Diane Cross is the owner of Bent Cycling, a small company in Longmont, Colorado, that designs and manufactures custom-made recumbent bicycles. Recumbents or bents are bicycles on which the rider sits in a reclined position with the pedals placed forward rather than below the feet. Interest in recumbents has increased in the past few years as more cyclists discover that recumbent bikes put less strain on the lower back than traditional bikes. This interest has carried over to Dianes company. By properly marketing Bent Cyclings products, market analysts believe her company could double its annual revenue in five years. However, that would entail completely rebuilding the companys workshop and design facility, which requires a major investment of time and money and poses some financial risk. Diane wants to use Excel to explore the different financing options available to her and to determine the profitability of such a venture. Excel supports a wealth of financial functions for just this kind of task.
Session 9.2 Interpolate and extrapolate a series of values Calculate a depreciation schedule Session 9.3 Determine a payback period Calculate a net present value Calculate an internal rate ofreturn Trace a formula error to itssource
S Tar T ing DaTa File S
Excel9
Tutorial
Review
Case1
Case2
Case3
Case4
Not For Sale
EX 09 Tutorial C7483 46419.indd 489
Financial.xlsx Income.xlsx Investment.xlsx Return.xlsx
Building.xlsx Condo.xlsx Loan.xlsx NPV.xlsx Statement.xlsx
College.xlsx Witte.xlsx
Blue.xlsx
EX 489
8/23/10 12:26 PM
EX 490
Excel | Tutorial 9 Working with Financial Tools and Functions
Not For Sale
s essi O n 9.1 v isual Overview
The FV func tion calculates the future value of a loan or an investment.
The PV func tion calculates the present value of a loan or an investment.
A negative cash flow (or cash outflow) is a negative value that represents cash going away from the investor. A positive cash flow (or cash inflow) is a positive value that represents money coming to the investor.
EX 09 Tutorial C7483 46419.indd 490
8/23/10 12:26 PM
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
Tutorial 9 Working with Financial Tools and Functions | Excel
EX 491
lOan and i nvestment Functi O ns
The PMT function calculates the payment in each period required to pay off a loan or reach an investment goal.
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
The RATE function calculates the per period interest rate required to pay of f a loan or reach an investment goal.
The NPER function calculates the number of payments required to pay off a loan or reach an investment goal.
An amortization schedule provides the schedule of payments used to pay of f a loan.
The PPMT func tion calculates the amount of a loan payment applied to the principal.
The IPMT func tion calculates the amount of a loan payment used to pay of f the interest.
Not For Sale
8/23/10 12:26 PM
EX 09 Tutorial C7483 46419.indd 491
EX 492
Excel | Tutorial 9 Working with Financial Tools and Functions
Not For Sale
Evaluating Investment Options
Diane has been meeting with an industrial architect for the past few months to design Bent Cyclings new workshop and design facility. She wants the workshop to accommodate the increased demand for the companys cycles and incorporate the latest manufacturing techniques to deliver a quality product for the consumer. The new workshop will cost $300,000. Her business does not have that much available in ready capital, so Diane will have to look for other sources to finance the companys expansion. Diane asks you to create a financial analysis of the situation. She wants to know what options are available to finance the expansion and how much each option will cost. Diane has already created Excel workbooks containing many of the worksheets youll need to do the analysis.
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
8/23/10 12:26 PM
To open Dianes workbook:
1. Open the Investment workbook located in the Excel9\Tutorial folder included
with your Data Files, and then save the workbook as Investment Analysis in the same folder.
2. In the Documentation worksheet, enter your name in cell B3 and the date in cell B4. 3. Switch to the Investment worksheet.
Bent Cycling has $100,000 in cash reserves. Because this amount is not large enough to finance the proposed expansion, Diane can (1) continue to save money until her company can afford to build the new workshop and design facility, or (2) borrow the money and start building immediately. Diane has always financed her company without going deeply into debt. So, she first wants to examine whether the company can save enough money from its cash receipts to finance the proposed expansion in the near future. To answer that question, youll use the FV, PV, RATE, NPER, and PMT functions to calculate the impact of investing money into interest-bearing accounts. In each case, the financial function returns the value of one financial variable based upon the values of the other four.
Working with Loans and Investments REFERENCE
EX 09 Tutorial C7483 46419.indd 492
To calculate the present value of a loan or an investment, use the PV function. To calculate the future value of a loan or an investment, use the FV function. To calculate the size of the monthly or quarterly payments required to pay off a loan or meet an investment goal, use the PMT function. To calculate the number of monthly or quarterly payments required to pay off a loan or meet an investment goal, use the NPER function. To calculate the interest on a loan or an investment, use the RATE function.
Calculating a Periodic Payment with the PMT Function
Currently, the $100,000 is invested in a money market fund that pays 5.4% annual interest, compounded monthly. Diane wants to know how much the company would have to add to this fund each month so that the value of the fund will reach $300,000 18 months from now. In other words, Diane wants to know the periodic payment, or PMT value, needed to reach her goal. To perform this calculation, you use the PMT function
=PMT(rate,nper,pv[,fv=0][,type=0])
where rate is the interest rate per period, nper is the number of payment periods, pv is the present value of the investment or loan, and fv is the future value of the loan or
Tutorial 9 Working with Financial Tools and Functions | Excel
EX 493
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
The financial functions automatically format calculated values as currency; negative cash flows appear in a red font within parentheses.
investment. The fv argument is optional. If you dont include an fv value, Excel assumes a future value of 0. The PMT function, like the other Excel financial functions dealing with loans and investments, also includes an optional type argument. The type argument specifies whether payments are made at the end of each period (type=0) or at the beginning (type=1). The default is type=0, which is what youll use with the calculations involving Dianes investments in the money market fund. The interest rate and the payment period must use the same time unit. For example, if the interest rate is compounded monthly, the payment period must also be in months. Because the money market fund Diane uses has an annual interest rate of 5.4%, compounded monthly, you need to convert the annual interest rate to a monthly rate, which is 1/12 of 5.4%, or 0.45%. Diane plans to invest the money for 18 months, so the value of the nper argument is 18 because each month represents a payment period. The PMT function, like the other four Excel financial functions, can be used with either investments or loans. The difference between a loan and an investment is based on the direction of the cash flow. When using the PMT function, the loan amount is treated as a positive cash value because it represents a payment to the company and the periodic payments used to pay off the loan are negative cash values. Its just the opposite with investments. The initial investment is treated as a negative cash value, and the periodic returns from the investment are positive cash values. For example, consider Dianes plan to invest in a money market fund. Bent Cycling starts out with $100,000, which Diane takes out of the company and places into an investment fund. This represents a negative cash flow because money is leaving the company. After 18 months supplemented by additional monthly payments, Diane takes money out of the fund and moves it back into the company to pay for the expansion. This represents a positive cash flow because now the money is flowing back into Bent Cycling. Cash flow, whether positive or negative, has nothing to do with who owns the money. Bent Cycling still owns the $100,000 it invests even if the money is transferred into an investment account. Cash flow is solely concerned with the direction of the money as it moves into and out of the company. When applied to the PMT function, the value of the pv (present value) argument for Dianes investment is $100,000 to indicate a negative cash flow as the money leaves the company to go into the money market fund. At the end of 18 months, Diane wants to retrieve $300,000 from the fund and put it back into the company, so the value of the fv (future value) argument is $300,000 to indicate a positive cash flow. Based on this information, the PMT function to calculate the monthly payment Bent Cycling must make to increase the value of the money market fund from $100,000 to $300,000 in 18 months is:
=PMT(5.4%/12,18,-100000,300000)
This formula returns the currency value $10,242.15, indicating that the company must invest a little more than $10,000 each month to meet its savings goal of $300,000 after 18 months. The value returned by the PMT function is negative in this case because it represents the amount of money flowing out of the company into the fund each month. It is good practice to reference worksheet cells in which the values you will use in the function are entered so that the values are easily visible. Youll enter the PMT function to calculate the monthly payment the company must make to reach its savings goal. Diane has already formatted the worksheet.
To calculate the monthly payment to the investment fund:
1. In cell A5, enter Monthly Payment (PMT). 2. In cell B5, enter 100,000, which is the amount of the initial investment. You
Not For Sale
EX 09 Tutorial C7483 46419.indd 493
enter a negative value to indicate a cash outflow from the company into the money market fund. The value appears as ($100,000) in a red font, representing a negative cash flow, because Diane already formatted the worksheet.
8/23/10 12:26 PM
EX 494
Excel | Tutorial 9 Working with Financial Tools and Functions
Not For Sale
future value of the investment to be. The value appears as $300,000 to indicate a positive cash flow. est rate into a monthly interest rate.
3. In cell C5, enter 300,000 for the investment goal, which is what you want the
4. In cell D5, enter 5.4% for the annual interest rate. 5. In cell E5, enter 12 for the number of months needed to convert the annual inter 6. In cell F5, enter the formula =D5/E5 to calculate the interest rate per month, and
then format the value as a percentage to two decimal places. into the fund.
7. In cell G5, enter 18, which is the number of months that Diane will transfer money
You can insert the PMT function and view its arguments and meanings using the Insert Function and Function Arguments dialog boxes by clicking the Insert Function button on the formula bar.
8. In cell H5, enter the formula =G5/E5 to calculate the number of years that the
money will be invested.
9. In cell I5, enter the formula =PMT(F5,G5,B5,C5) to calculate the monthly payment (PMT). The formula returns the negative currency value ($10,242.15).
10. Select cell I5, and then apply the Output cell style. See Figure 9-1.
monthly payment to reach the investment goal
Figure 9-1
PMT function initial cash outflow final cash inflow interest rate per month months to invest monthly cash outflow
Calculating a Future Value with the FV Function
Bent Cycling needs to divert a little more than $10,000 per month from cash receipts into the investment fund to reach a savings goal of $300,000 in the next year and a half. However, Bent Cycling can afford to invest at most $7,500 each month. Diane wants to know how much the fund would be worth in 18 months if the company puts $7,500 a month into the fund. To calculate the funds future value, you use the FV function
=FV(rate,nper,pmt[,pv=0][,type=0])
where the rate, nper, pmt, and type arguments still represent the interest rate per period, the number of payments, the payment each period, and when the payment is due (beginning or end of the period). The pv argument is optional and represents the present value of the investment, which is assumed to be zero if no value is specified. The type argument is also optional and, as with the PMT function, indicates when payments are made into the loan or investment. For the Bent Cycling investment, these function argument values are:
=FV(5.4%/12,18,-7500,-100000)
EX 09 Tutorial C7483 46419.indd 494
8/23/10 12:26 PM
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
Tutorial 9 Working with Financial Tools and Functions | Excel
EX 495
As with the PMT function, the interest rate is 5.4% divided by 12, the total number of payments is 18, the amount invested each month is 7,500 (a negative cash flow), and the present value is 100,000, also a negative cash flow because it represents money that has already been invested by the company. Youll use the FV function to calculate the future value of the money market fund under this investment strategy.
To calculate the future value of the investment:
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
1. In cell A6, enter Investment Goal (FV). 2. Copy the initial investment value in cell B5 to cell B6. 3. Copy the rate and payment period formulas and values in the range D5:H5 to the
range D6:H6.
4. In cell I6, enter 7,500 as the present value, which is the monthly payment Bent
Cycling can afford.
5. In cell C6, enter the formula =FV(F6,G6,I6,B6). The formula returns the value
$248,707, which is the value of the fund after 18 months (cell G6) of depositing $7,500 each month (cell I6) at a 0.45% monthly interest rate (cell F6). The value is positive, indicating a positive cash flow from the fund back to the company.
6. Format cell C6 using the Output cell style. See Figure 9-2.
Figure 9-2 Future value of the investment
FV function
final value of the investment
monthly payment proposed by Diane
Calculating an Investments Length with the NPER Function
By limiting the monthly deposit to $7,500, Bent Cycling could grow its savings to about $250,000 in the next year and a half. However, the expansion cannot be done for less than $300,000. Diane wants to know how long it would take to save $300,000 with a monthly deposit of $7,500. To calculate this, you must determine the number of payments that must be made into the fund. This is done with the NPER function
=NPER(rate,pmt,pv[,fv=0][,type=0])
Not For Sale
=NPER(5.4%/12,-7500,-100000,300000)
EX 09 Tutorial C7483 46419.indd 495
whose arguments have the same meaning as the other financial functions: the rate argument is the interest rate per period, the pmt argument is the payment amount per period, and the pv argument is the present value of the loan or investment. The fv argument storing the future value of the loan or investment is optional, and has a default value of 0. For the Bent Cycling investment, you use the following formula:
8/23/10 12:26 PM
EX 496
Excel | Tutorial 9 Working with Financial Tools and Functions
Not For Sale
Again, the interest rate per month is 5.4%/12 or 0.45%, the monthly cash outflow is 7,500, the initial value invested is also a cash outflow of 100,000, and the future value, or savings goal, is a cash inflow of 300,000. The important point to remember about the NPER function is that it returns the number of payment periods, not necessarily the number of years. Recall that the financial functions are based on the time interval in which interest is compounded. So, if you are making periodic monthly payments and the interest is compounded monthly, the number of periods is also in months, not years.
If the NPER function returns #NUM!, the loan cannot be repaid in any length of time. This occurs when the monthly payments are less than the monthly interest charges, making it impossible to repay the loan.
1. In cell A7, enter Months (NPER). 2. Copy the values and formulas in the range B5:F5 to the range B7:F7. 3. Copy the values and formulas in the range H6:I6 to the range H7:I7. 4. In cell G7, enter the formula =NPER(F7,I7,B7,C7). The formula returns the value 23.886, which indicates that at the current interest rate (cell F7) with a monthly payment of $7,500 (cell I7), the number of months required to meet the investment goal (cell C7) would be almost 24 months, or 2 years.
5. Format cell G7 with the Output cell style. See Figure 9-3.
Figure 9-3 number of payments needed to reach the investment goal
NPER function
months needed to meet the savings goal
Calculating an Investments Present Value with the PVFunction
It will take about two years for Bent Cycling to save $300,000 by making monthly payments of $7,500. With consumers increasing interest in recumbents, Diane does not want to wait that long to upgrade the companys production facilities. Diane wonders whether Bent Cycling could reach its savings goal within the required 18 months by increasing the amount of the initial $100,000 investment. To determine how much the company must place into the account to reach its savings goal of $300,000 in 18months, you must find the investments present value by using the PV function
=PV(rate,nper,pmt[,fv=0][,type=0])
where rate is the interest per period, nper is the number of payments, pmt is the payment made each period, fv is the future value, and type is when the payments are made within each period. For the Bent Cycling project, the following formula returns the present value
EX 09 Tutorial C7483 46419.indd 496
8/23/10 12:26 PM
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
To calculate the number of payments required to reach an investment goal:
Tutorial 9 Working with Financial Tools and Functions | Excel
EX 497
of an investment needed to reach a future value of $300,000, assuming 18 additional payments of $7,500 at an annual interest rate of 5.4% interest compounded monthly:
=PV(5.4%/12,18,-7500,300000)
Youll find out how much the initial investment must be for Diane to reach her investment goal.
To calculate the present value of an investment needed to reach the investment goal:
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
1. In cell A8, enter Initial Investment (PV). 2. Copy the value in cell C7, and then paste it into cell C8. 3. Copy the values in the range D6:I6 and paste them into the range D8:I8. 4. In cell B8, enter the formula =PV(F8,G8,I8,C8). The formula returns the value ($147,311), which is how much Diane needs in the account today to reach $300,000 in savings after 18 months of investing $7,500 per month at 5.4% annual interest.
5. Format cell B8 using the Output cell style. See Figure 9-4.
Figure 9-4 Present value of the investment
PV function
initial investment needed to reach savings goal
Diane would have to increase the amount currently invested in the fund from $100,000 to almost $150,000 to reach her savings goal. The company doesnt have another $50,000 in ready cash assets to invest, so this is not a reasonable option for Diane.
Calculating an Investments Interest Rate with the RATE Function
To calculate an annual rate, multiply the value returned by the RATE function by the number of payments per year. For monthly payments, multiply the rate value by 12.
With the 5.4% interest rate offered by the fund, Bent Cycling cannot save enough money in the next 18 months to finance the expansion. Diane wants to know what interest rate would be needed to reach her savings goal. To answer that question, youll use the RATE function
=RATE(nper,pmt,pv[,fv=0][,type=0])
Not For Sale
=RATE(18,-7500,-100000,300000)
EX 09 Tutorial C7483 46419.indd 497
where nper is the number of payments, pmt is the amount of each payment, pv is the investments present value, fv is the investments future value, and type defines when the payments are made. To calculate the interest rate for the Bent Cycling investment, you use the following formula with the RATE function:
8/23/10 12:26 PM
EX 498
Excel | Tutorial 9 Working with Financial Tools and Functions
Not For Sale
The value returned by the rate function is based on 18 payments of $7,500 each with an initial investment of $100,000 that results in a future value of $300,000. The value returned by the RATE function is the interest rate per period, not the interest rate per year.
To calculate the interest rate required to reach the investment goal:
1. In cell A9, enter the text Rate per Month (RATE). 2. Copy the values and formulas in the range B7:C7 to the range B9:C9. 3. In cell E9, enter the value 12 and then copy the values and formulas in the range
G8:I8 to the range G9:I9. which uses the worksheet cells for the number of payments (cell G9), the monthly payments (cell I9), the present value (cell B9), and the future value (cell C9). The function returns the value 1.91%, which represents the monthly interest rate. 12 months to calculate the annual interest rate of 22.95%.
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
8/23/10 12:26 PM
4. In cell F9, enter the formula =RATE(G9,I9,B9,C9) to calculate the interest rate,
5. In cell D9, enter the formula =F9*E9 which multiplies the monthly interest rate by 6. Format cell F9 with the Output cell style. See Figure 9-5.
Figure 9-5 interest rate for the investment
RATE function
annual interest rate
monthly interest rate needed to reach savings goal
Diane needs an investment with an annual interest rate of 22.95% compounded monthly. Because no such investment opportunity is available, Diane decides that the company will have to borrow the money to fund the expansion.
Working with Loans and Mortgages
Diane asks you to determine how much it will cost Bent Cycling to borrow the entire $300,000 rather than dipping into its cash reserves. A local bank will lend the entire amount at 8.5% annual interest compounded quarterly. Diane wants to repay the loan in five years. She needs you to calculate the quarterly payments to repay the loan within the specifiedtime.
EX 09 Tutorial C7483 46419.indd 498
Tutorial 9 Working with Financial Tools and Functions | Excel
EX 499
Calculating a Loan Payment
Loans and investments are calculated with the same Excel functions. The only difference is the direction of the cash flow. When you borrow money, the money you receive represents a positive cash flow because the money flows to you. As you repay the loan, each payment represents a negative cash flow because you are sending money back to the lending institution. To calculate the quarterly payment for the loan, you use the PMT function. Because the interest is compounded quarterly, the value of the rate argument is 8.5% divided by 4, or 2.125%. The value of the nper argument is 20, which is the number of quarterly payments for the five-year period. The value of the pv argument is $300,000, the present value of the loan. The value is positive because cash is flowing into the company. The value of the fv argument is 0 because the company will repay the loan in full, making the future value of the loan 0 (that is, the company will not owe the bank anything after repaying the loan). Using these values, the complete PMT function is:
=PMT(8.5%/4,20,300000)
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
This formula doesnt include the optional fv argument. When omitted, Excel assumes a future value of 0, which is what you want. Youll enter this function into the Loan Schedule worksheet.
To calculate the monthly payment for the loan:
1. Switch to the Loan Schedule worksheet. 2. In cell A5, enter $300,000. The loan amount is entered as a positive cash flow
because the money is going from the bank to Bent Cycling.
3. In cell B5, enter 8.5% which is the annual interest rate. 4. In cell C5, enter 4 which is the number of quarters in the year. 5. In cell D5, enter the formula =B5/C5. The formula returns the value 2.13%, which
is a quarterly interest rate of 2.125% displayed to two decimal places.
6. In cell E5, enter 5 which is the number of years needed to repay the loan. 7. In cell F5, enter the formula =C5*E5 to calculate the total number of quarters to
repay the loan.
8. In cell G5, enter the formula =PMT(D5,F5,A5). The formula returns the value
($18,569), which is a negative cash flow displayed to the nearest dollar amount. See Figure 9-6.
Figure 9-6
Quarterly loan payment
quarterly payment to repay the $300,000 loan in five years
Not For Sale
EX 09 Tutorial C7483 46419.indd 499 8/23/10 12:26 PM
EX 500
Excel | Tutorial 9 Working with Financial Tools and Functions
Not For Sale
Bent Cycling could repay the loan in full with quarterly payments of $18,569 over the next five years. This is about $6,200 per month, which the company can afford.
Creating an Amortization Schedule
An amortization schedule is a table that specifies how much of each loan payment is devoted toward interest and toward repaying the principal. The principal is the amount of the loan that is still unpaid. When repaying loans, the initial payments mostly pay off the interest. As more of the loan is repaid, the percentage of each payment used for interest decreases (because less of the loan is left to charge interest upon) until the last few payments are used almost entirely for paying off the principal. Figure 9-7 shows a typical relationship between the amount paid toward interest and the amount paid toward the principal plotted against the number of payments. Figure 9-7 interest and principal payments
later payment periods apply more of the payment toward the principal
later payment periods apply less of the payment toward the interest
To calculate the amount of a loan payment devoted to interest and to principal, you can use the IPMT and PPMT functions. The IPMT function returns the amount of a particular payment that is used to pay the interest on the loan and has the syntax
=IPMT(rate,per,nper,pv[,fv=0][,type=0])
where the rate, nper, pv, fv, and type arguments have the same meaning as they do for the PMT function. The per argument contains the period for which you want to calculate the interest due. For example, the following formula calculates how much interest is due in the fifth payment of the $300,000 loan that Diane is contemplating:
=IPMT(8.5%/4,5,20,300000)
The formula returns the value $5,304.99, which means that about $5,300 in interest is paid to the bank in the fifth payment. The fv argument was omitted because the loan will be paid off in full. The PPMT function, which calculates the amount used to repay the principal, is similar. It has the following syntax:
=PPMT(rate,per,nper,pv[,fv=0][,type=0])
EX 09 Tutorial C7483 46419.indd 500
8/23/10 12:26 PM
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
Tutorial 9 Working with Financial Tools and Functions | Excel
EX 501
The following formula calculates the amount of the principal that is repaid with the fifth payment:
=PPMT(8.5%/4,5,20,300000)
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
The formula returns the value $13,264.08, which means that the amount owed to the bank is reduced by about $13,200 after the fifth payment on the loan. Note that the sum of the interest payment and the principal payment is $18,569.08, which is the same value returned by the PMT function earlier. The total amount paid to the bank each month doesnt change, only how that amount is allocated between paying the interest and paying off the principal. Diane wants to know how much of the $18,569 is being used to pay interest on the loan and how much is being used to repay the principal. You will create an amortization schedule for the proposed loan. Diane has already created the schedules structure. Youll enter the formulas to calculate the monthly payments on the loans interest and principal.
To create the amortization schedule for the loan:
1. Click cell C9, type $300,000 and then press the Tab key. Column C lists the
amount of the principal remaining on the loan, so the initial value in cell C9 is the amount of the loan. key. The formula returns the value ($6,375), a negative cash flow in which $6,375 is spent on interest during the first quarter of the loan.
2. In cell D9, enter the formula =IPMT($D$5,B9,$F$5,$A$5) and then press the Tab
In an amortization schedule, use absolute references to refer to the original loan conditions and use relative references to refer to information about specific payment periods.
3. In cell E9, enter the formula =PPMT($D$5,B9,$F$5,$A$5) and then press the Tab
key. The formula returns the value ($12,194), indicating a negative cash flow in which $12,194 is spent to reduce the principal during the first quarter of the loan. period of the loan. The formula returns ($18,569), which matches the quarterly payment value shown in cell G5. See Figure 9-8.
4. In cell F9, enter the formula =D9+E9 to calculate the total payment for the first
Figure 9-8
initial payment in the amortization schedule
interest due in the first quarter
principal paid off in the first quarter
total first quarter payment
Not For Sale
EX 09 Tutorial C7483 46419.indd 501
The formulas for the rest of the amortization schedule are similar to that for the first quarter except that the remaining principal in column C must be reduced by the amount paid toward the principal in the previous quarter.
8/23/10 12:26 PM
EX 502
Excel | Tutorial 9 Working with Financial Tools and Functions
Not For Sale
To complete the amortization schedule:
returns $287,806, which is the amount of the principal remaining at the start of the second quarter of the loan. calculate the interest, principal, and total payment for the second quarter. The interest payment is ($6,116), the principal payment is ($12,453), and the total payment is ($18,569). The interest due for the second quarter is less than for the first quarter because the remaining principal is lower. As a result, more of the total payment for this quarter can be used to reduce the remaining principal. C10:F28. The formulas are copied into the rest of the rows of the amortization schedule to calculate the remaining principal, interest payment, principal payment, and total payment for each of the remaining 18 quarters of the loan.
1. In cell C10, enter the formula =C9+E9 and then press the Tab key. The formula 2. Copy the range D9:F9, and then paste the formulas into the range D10:F10 to
3. Select the range C10:F10, and then drag the fill handle down to select the range
4. Click the Auto Fill Options button, and then click the Fill Without Formatting
option button. The formulas are entered without overwriting the existing formatting in the worksheet.
5. In cell C29, enter the formula =C28+E28 to calculate the final balance of the loan
after the final payment. See Figure 9-9.
Figure 9-9 completed amortization schedule
A table of red values enclosed in parentheses can be difficult to read. Amortization schedules often format all entries as positive values, and then label the columns to indicate the values represent negative cash flows.
quarterly interest payments quarterly payments toward the remaining principal quarterly loan payments
final balance after the last payment
In the last quarterly payment at the end of the fifth year, only $386 of the $18,569 payment is used to pay the interest on the loan. The remaining $18,183 is used to pay the principal. The last principal payment is equal to the amount of the remaining principal, creating a final balance of $0. This is to be expected because the goal is to completely repay the loan by the end of the fifth year.
Calculating Cumulative Interest and Principal Payments
The breakdown of the quarterly payments in the amortization table is helpful. However, companies usually create annual budgets, not quarterly budgets. So companies want to know the total interest payments and principal payments during each year of the
EX 09 Tutorial C7483 46419.indd 502
8/23/10 12:26 PM
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
Tutorial 9 Working with Financial Tools and Functions | Excel
EX 503
loan. You can calculate these cumulative payments on interest and principal using the CUMIPMT and CUMPRINC functions. The CUMIPMT function calculates the sum of several interest payments and has the syntax
=CUMIPMT(rate,nper,pv,start,end,type)
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
where rate is the interest rate per period, nper is the total number of payment periods, pv is the present value of the loan, start is the starting payment period for the interval you want to sum, end is the ending payment period, and type defines whether the payments are made at the beginning (0) or end (1) of each period. This function has no fv argument; the assumption is that loans are always completely paid off. Also, note that the type argument is not optional. To calculate the cumulative total of payments made toward the principal, you use the CUMPRINC function, which has the syntax
=CUMPRINC(rate,nper,pv,start,end,type)
where the rate, nper, pv, start, end, and type arguments have the same meaning as they do for the CUMIPMT function. For example, to calculate the total interest payments of the loan in the second year of the amortization schedule, you would enter the following formula:
=CUMIPMT(8.5%/4,20,300000,5,8,0)
The starting and ending periods have values of 5 and 8, which represent the fifth through eighth quarters of the payment schedule. The formula returns ($19,505), indicating a negative cash flow in which about $19,500 is spent on interest payments in the second year of the loan. The value of the type parameter is 0 to indicate that the loan payments are due at the start of each quarter. To calculate how much is spent when paying back the principal in that same time period, you would enter the following formula, which returns ($54,772), indicating that almost $55,000 will be spent in the second year repaying the principal on the $300,000loan:
=CUMPRINC(8.5%/4,20,300000,5,8,0)
Youll use both the CUMIPMT and CUMPRINC functions to calculate the total interest and principal payments for this proposed loan in each of the five years of the amortization schedule. The terms of the loan are already entered at the top of the worksheet in cell D5 (rate argument), cell F5 (nper argument), and cell A5 (pv argument). The table at the bottom of the Loan Schedule worksheet already has the starting and ending quarters for each of the five years of loan. Youll enter formulas with the CUMIPMT and CUMPRINC functions to calculate the yearly payments for the interest and the principal.
To calculate the yearly interest and principal payments:
1. In cell B36, enter the formula =CUMIPMT($D$5,$F$5,$A$5,B34,B35,0) to calculate
the interest for the first year. The formula returns ($23,923), indicating that nearly $24,000 is paid toward interest in the first four quarters or the first year of the loan.
2. Copy the formula in cell B36 to the range C36:F36. The absolute references for
the rate, nper, and pv arguments always reference the loan conditions from the top of the worksheet ($D$5, $F$5, and $A$5, respectively) and the relative references for the starting and end parameters are updated in each table row. The yearly interest paid is calculated for the remaining four years of the loan, concluding with ($3,784), the amount paid in the fifth and final year. calculate the principal payment in the first year. The formula returns ($50,353), indicating that more than $50,000 will be spent in the first year toward reducing the size of the principal. $70,492 will be used to pay off the principal.
3. In cell B37, enter the formula =CUMPRINC($D$5,$F$5,$A$5,B34,B35,0) to
4. Copy the formula in cell B37 to the range C37:F37. In the final year of the loan,
Not For Sale
EX 09 Tutorial C7483 46419.indd 503
8/23/10 12:26 PM
EX 504
Excel | Tutorial 9 Working with Financial Tools and Functions
Not For Sale
interest. principal. See Figure 9-10.
interest and principal payments
5. In cell G36, enter the formula =SUM(B36:F36) to calculate the total payment for 6. In cell G37, enter the formula =SUM(B37:F37) to calculate the total payment for
Figure 9-10
total interest paid entire loan is repaid
interest and principal payments during year 3 (quarter 9 to quarter 12)
7. Save the workbook, and then close it.
At the end of five years, Bent Cycling will have spent more than $370,000 ($300,000 for the principal repayment plus $71,382 for interest) to receive $300,000 today.
PROSKILLS
Written Communication: Writing a Financial Workbook
The goal of all writing, whether a report or a worksheet, is communication. A properly written financial workbook should be simple for others to read and understand. It should also be easily edited to explore what-if scenarios, allowing your company or group to analyze the impact of different financial conditions on the bottom line. To help ensure that any financial workbook you create meets these goals, keep in mind the following principles: Place all important financial variables at or near the top of a worksheet so that they can be easily read by others. For example, place the interest rate you use in calculations in a well-labeled worksheet cell. Use defined names with the financial variables to make it easier to apply them in formulas and functions. Clearly identify the direction of the cash flow in all of your financial calculations. Most Excel financial functions require a particular direction to the cash flow to return the correct value. Using the wrong sign will turn the calculation of a loan payment into an investment deposit or vice versa. Place argument values in worksheet cells where they can be viewed and easily changed. Never place these values directly into a financial formula. Use the same unit of time for all the arguments in a financial function. For example, when using the PMT function to calculate monthly loan payments, the interest rate and the number of payments should be based on the interest rate per month and the total months to pay off the loan. A financial workbook that is easy to read and understand is more useful to yourself and others as you explore the financial aspects of business decisions.
EX 09 Tutorial C7483 46419.indd 504
8/23/10 12:26 PM
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
Tutorial 9 Working with Financial Tools and Functions | Excel
EX 505
So far, you have analyzed different investment and loan options for Bent Cycling, which will help Diane decide how to finance the potential expansion. In the next session, youll make projections about the companys future earnings assuming Diane decides to finance the expansion.
Session 9.1 Quick Check R EVIEW
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
EX 09 Tutorial C7483 46419.indd 505
1. Explain the difference between positive and negative cash flow. If you borrow $10,000 from a bank, is that a positive or negative cash flow? Justify your answer. 2. Use Excel to calculate how much a savings account would be worth if the initial balance is $500 and you deposit $50 per month for 10 years at 5.8% annual interest compounded monthly. 3. You want a savings account to grow from $1,000 to $3,000 within two years. Assume the bank provides a 5.2% annual interest rate compounded monthly. Calculate how much you must deposit each month to meet your savings goal. 4. You want to take out a loan for $200,000 at 7% interest compounded monthly. If you can afford to make monthly payments of only $1,500 on the loan, how many months will it take you to pay off the loan completely? 5. Rerun your calculations from the previous question assuming that you can afford only a $1,000 monthly payment. What value does Excel return? How do you explain the result? 6. You take out a loan for $200,000. The loan must be repaid in 10 years with quarterly payments of $7,200. Under those terms, what is the annual interest rate of the loan? 7. You take out a 10-year loan for $150,000 at 6.3% interest compounded monthly. What is the monthly payment? How much of the first payment is used for interest and how much is used to pay off the principal? 8. For the loan conditions specified in Question 7, calculate how much interest you will pay in the first year and how much will you pay toward the principal.
Not For Sale
8/23/10 12:26 PM
EX 506
Excel | Tutorial 9 Working with Financial Tools and Functions
Not For Sale
s essi O n 9.2 v isual Overview
Cost of goods sold consists of expenses directly related to production.
General expenses are expenses not directly related to produc tion.
Depreciation is the proces s of allocating the original cost of an as set over the lifetime of the as set.
EX 09 Tutorial C7483 46419.indd 506
8/23/10 12:26 PM
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
An income statement (also called a profit and loss statement) displays how much money a person or business makes or loses over a specified period of time.
Tutorial 9 Working with Financial Tools and Functions | Excel
EX 507
i nc O me s tatement and d e P reciati O n
Interpolation is used to f ill in a series when you know the star ting and ending values of that series.
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
Extrapolation is used to extend a series from a single value or a few values in order to projec t future values.
A linear trend changes values by a cons tant amount.
A grow th trend changes values by a cons tant percentage.
The DB func tion calculates the declining balance depreciation in which the as set depreciates by a cons tant percentage each time period.
Not For Sale
EX 09 Tutorial C7483 46419.indd 507 8/23/10 12:26 PM
EX 508
Excel | Tutorial 9 Working with Financial Tools and Functions
Not For Sale
Projecting Future Income and Expenses
An income statement shows how much money a business makes or loses over a specified period of time. Income statements are often created monthly, semiannually, or annually. Bent Cycling will pay more than $70,000 in interest payments by the time the proposed loan is repaid. Diane wants to know how this additional expense will affect the companys income over the next five years. She is interested only in annual projections. To provide Diane with an answer, youll complete an income statement. Diane already created the layout youll use.
1. Open the Income workbook located in the Excel9\Tutorial folder included with 2. In the Documentation worksheet, enter your name in cell B3 and the date in
cell B4.
your Data Files, and then save the workbook as Income Projection in the same folder.
3. Switch to the Income Statement worksheet. The Income Statement worksheet
contains columns for income and expense projections over the next five years and is divided into the five sections shown in Figure 9-11.
Figure 9-11
income statement worksheet
gross profit (revenue minus cost of producing sold goods) general expenses (expenses not directly related to the cost of producing sold goods) predicted earnings before taxes (operating profit minus interest expenses) predicted net income (pretax earnings minus estimated tax) operating profit (gross profit minus general expenses and depreciation of tangible assets)
EX 09 Tutorial C7483 46419.indd 508
8/23/10 12:26 PM
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
To open the income statement workbook:
Tutorial 9 Working with Financial Tools and Functions | Excel
EX 509
Exploring Linear and Growth Trends
Diane wants to project the companys future revenue. She sees two possibilities: (1)revenue will grow by a constant amount from year to year, or (2)revenue will grow by a constant percentage each year. Dianes first possibility, in which the values change by a constant amount, is an example of a linear trend. When plotted, a linear trend appears as a straight line. The second possibility, in which the values change not by a constant amount but by a constant percentage, is an example of a growth trend. For example, each value in a growth trend might be 15% higher than the previous years value. When plotted, a growth trend appears as a curve with the greatest increases occurring near the end of the series. Figure9-12 shows a linear trend and a growth trend for revenue that starts at $1.8 million in Year 1 and doubles to $3.6 million by Year 5. The growth trend lags behind the linear trend in the early stages but reaches the same stopping value at the end of the timeperiod. comparison of linear and growth trends
Growth trend calculations are also called exponential growth trends or exponential trends in the fields of economics and statistics.
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
Figure 9-12
Interpolating within a Series of Values
If you know the beginning and ending values in a series of values and whether the values constitute a linear or growth trend, you can use AutoFill to fill in the missing values. You will use AutoFill to interpolate Bent Cyclings future revenue using a linear trend between Year 1 and Year 5.
To project the companys future revenue based on a linear trend:
1. In cell B6, enter 1,800,000 which is the projected revenue for the first of the next
five years.
2. In cell F6, enter 3,600,000 which represents a doubling of the companys pro-
Not For Sale
EX 09 Tutorial C7483 46419.indd 509 8/23/10 12:26 PM
jected revenue by the end of the five-year period.
EX 510
Excel | Tutorial 9 Working with Financial Tools and Functions
Not For Sale
this range. Series. The Series dialog box opens. , and then click and then click the Trend check box to insert a check mark. See Figure 9-13.
series dialog box
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
8/23/10 12:26 PM
3. Select the range B6:F6. The projected sales revenue for each year will appear in 4. In the Editing group on the Home tab, click the Fill button
5. Verify that the Rows option button and the Linear option button are selected,
Figure 9-13
fill the series assuming a linear trend fill the series in a row
select the Trend box
To interpolate a series, the cells between the first and last cells in the series must be blank.
6. Click the OK button. The values inserted into the range B6:F6 show the companys
projected revenue based on a linear trend. In this case, the revenue increases $450,000 per year from Year 1 through Year 5.
Next, youll interpolate the projected revenue using a growth trend. To interpolate the growth trend correctly, you first must remove the Year 2 through Year 4 values, leaving those cells blank.
To project the companys future revenue based on a growth trend:
1. Delete the values in the range C6:E6. 2. Select the range B6:F6. The projected sales revenue for each year will appear in
this range.
3. In the Editing group on the Home tab, click the Fill button
Series. The Series dialog box opens.
, and then click
4. In the Type section, click the Growth option button, click the Trend check box
to insert a check mark, and then click the OK button. The Year 1 through Year 5 revenue projections are now based on a growth trend. See Figure 9-14.
EX 09 Tutorial C7483 46419.indd 510
Tutorial 9 Working with Financial Tools and Functions | Excel
EX 511
Figure 9-14
Projected revenue based on a growth trend
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
Year 1 projected revenue
Year 2 through Year 4 values interpolated based on a growth trend
Year 5 projected revenue
Under the growth trend projections, the largest revenue increases occur near the end of the five-year period. For example, the revenue grows by about $340,000 from Year1 to Year 2, but by about $570,000 from Year 4 to Year 5. Calculating the percentage change in revenue from year to year under the growth trend projection rather than the absolute change in dollars shows that the projected revenue increase is about 19% each year. Diane is expecting the fastest growth a few years from now and asks you to base the income projections on a growth trend.
Projecting Future Expenses
The next part of the income statement displays the cost of goods sold. Bent Cycling needs to purchase basic parts and raw materials to manufacture recumbents. The more recumbents the company builds, the more parts and materials it must purchase, increasing the cost to the company. For every dollar of sales revenue earned, the company must spend 72 cents to cover the production costs. In other words, the cost of goods sold is about 72% of the revenue generated. The difference between the companys sales revenue and the cost of goods sold is the companys gross profit. Youll enter formulas to calculate the cost of goods sold and the gross profit.
To project the cost of goods sold and the gross profit:
1. In cell F3, enter 72%. 2. In cell B7, enter the formula =B6*$F$3. This formula multiplies projected sales 3. In cell B8, enter the formula =B6B7. This formula subtracts the cost of goods
sold from the sales revenue to project a gross profit of 504,000 for Year 1. gross profit for each of the next five years. See Figure 9-15.
revenue by 72% to project the cost of goods sold, which is $1,296,000 for Year 1.
4. Copy the range B7:B8 to the range C7:F8 to project the cost of goods sold and
Not For Sale
EX 09 Tutorial C7483 46419.indd 511 8/23/10 12:26 PM
EX 512
Excel | Tutorial 9 Working with Financial Tools and Functions
Not For Sale
Projected gross profit
projected gross profit for the company percentage to calculate the cost of goods sold
Figure 9-15
Interpolating and Extrapolating a Series REFERENCE
To interpolate a series of values between starting and ending values: Select the range with the first cell containing the starting value, blank cells for middle values, and the last cell containing the ending value. In the Editing group on the Home tab, click the Fill button, and then click Series. Select whether the series is organized in rows or columns, select the type of series to interpolate, and then check the Trend check box. Click the OK button. To extrapolate a series from a starting value: Select a range with the first cell containing the starting value followed by blank cells to store the extrapolated values. In the Editing group on the Home tab, click the Fill button, and then click Series. Select whether the series is organized in rows or columns, select the type of series to extrapolate, and then enter the step value in the Step value box. Click the OK button.
Extrapolating from a Series of Values
When you extrapolate a series, the Step value represents the amount that each value is increased or multiplied as the series is extended. You do not have to specify a stopping value.
The next section of the income statement contains the projected general expenses for the company. These are expenses not directly related to production. For example, Bent Cycling must purchase insurance, provide for general maintenance and utilities, and pay for advertising regardless of the number of bikes it actually sells. Diane has projected values for Year 1 that she wants you to enter into the worksheet.
EX 09 Tutorial C7483 46419.indd 512
8/23/10 12:26 PM
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
Tutorial 9 Working with Financial Tools and Functions | Excel
EX 513
To enter the Year 1 general expenses:
1. Enter the following expenses in the range B10:B15:
cell B10: 10,000 cell B11: 14,000 cell B12: 9,000 cell B13: 10,000 cell B14: 8,000 cell B15: 10,000
2. In cell B16, enter =SUM(B10:B15) to calculate the total general expenses for
Year 1, which is 61,000.
Diane does not have projected expenses past the first year, but she expects that the proposed expansion will increase expenses by about 15% per year. To extend a series from one or more beginning values, you use extrapolation. Youll extrapolate the companys expenses over the next five years, assuming a growth trend in which the expenses increase by 15% each year. This is equivalent to multiplying each years expenses by 1.15 to project the next years expenses.
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
To extrapolate the Year 1 expenses through the next four years:
1. Select the range B10:F15. 2. In the Editing group on the Home tab, click the Fill button
Series. The Series dialog box opens.
Be sure to select the Rows option button so that the series values fill the row, not the column.
, and then click
3. Click the Rows option button because you want to create a series of values in the
rows of the selected range.
4. Click the Growth option button, enter 1.15 in the Step value box, and then click
the OK button. The expense values from Year 1 are extrapolated into the Year 2 through Year 5 columns. expenses for the company for each year.
5. Copy the formula in cell B16 to the range C16:F16 to project the total general
To extrapolate a trend that decreases rather than increases, use a step value of less than 0 for a linear trend, and between 0 and 1 for a growth trend.
These calculations show that the projected general expenses will rise to $106,689 by the end of Year 5. Next, you want to calculate the companys earnings during each of the next five years. The initial earnings estimate is equal to the companys gross profit minus the total general expenses.
To calculate the companys initial earnings:
1. In cell B18, enter the formula =B8B16 to calculate an initial estimate of the
yearly earnings, which is equal to the gross profit minus the total general expenses. This value is $443,000 for Year 1. through Year 5. See Figure 9-16.
2. Copy the formula in cell B18 to the range C18:F18 to project yearly earnings
Not For Sale
EX 09 Tutorial C7483 46419.indd 513 8/23/10 12:26 PM
EX 514
Excel | Tutorial 9 Working with Financial Tools and Functions
Not For Sale
Projected general expenses and earnings
Figure 9-16
projected general expenses
projected earnings
Calculating Depreciation of Assets
The financial status of a company includes information about the companys tangible possessions such as equipment, land, buildings, and vehicles. These combined assets are known as tangible assets because they are long-lasting material assets not intended for sale but only for use by the company. Not all material assets are tangible assets. For example, assets such as the raw materials Bent Cycling uses to manufacture bikes are not considered tangible assets because they are used in the production process and are sold indirectly to the consumer in the form of a finished bicycle. To calculate Bent Cyclings tax liability, you subtract any expenses the company incurs from the revenue it generates. However, the cost of building Dianes proposed workshop is neither income nor an expense. Its an investment in a tangible asset that will be used for many years to generate revenue for the company. Tax rules allow the original cost of the investment to be subtracted from the companys reported income, reducing the companys tax liability. In many cases, however, the entire cost of such a building project is not reported in a single tax statement. Instead, the original cost is allocated over the years in which the asset is used. For example, instead of deducting the entire $300,000 from the company reported income in Year 1, the company might only be able to deduct $60,000 per year for the next five years from its reported income.
EX 09 Tutorial C7483 46419.indd 514
8/23/10 12:26 PM
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
Tutorial 9 Working with Financial Tools and Functions | Excel
EX 515
This process of allocating the original cost of the investment over the years of use is known as depreciation. Different types of tangible assets have different rules for depreciation. However, in general, to calculate the depreciation of an asset, you need to know the following about the asset: The assets original cost The assets useful life The assets salvage value, which is the assets value at the end of its useful life The rate at which the asset is depreciated over time You already know the cost of the new workshop and design facility is $300,000. Given how quickly technology changes in bicycle design and manufacturing, Diane estimates the useful life of the new facility and its equipment is 15 years. After 15 years, she assumes the company will require another substantial upgrade of its facilities. At that point, Diane estimates that the salvage value of the old workshop will be one-third of its current value, or $100,000. Youll enter this information in the Depreciation worksheet.
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
To insert the depreciation parameters:
1. Switch to the Depreciation worksheet. 2. In cell B4, enter $300,000. This is the assets initial value. 3. In cell B5, enter $100,000. This is the assets estimated salvage value. 4. In cell B6, enter 15. This is the projected useful life of the asset in years.
The only thing remaining is to determine how quickly the new facility depreciates from its original cost of $300,000 to $100,000. Excel has five financial functions to calculate depreciation under different assumptions. Figure 9-17 summarizes these depreciation functions.
Figure 9-17
depreciation functions Function
SLN(cost, salvage, life) DB(cost, salvage, life, period[, month]) SYD(cost, salvage, life, period) DDB(cost, salvage, life, period[, factor=2])
Description
Returns the straight-line depreciation in which the asset declines by a constant amount each year, where cost is the initial cost of the asset, salvage is the salvage value, and life is the useful lifetime of the asset. Returns the declining balance depreciation in which the asset declines by a constant percentage each year, where period is the year of the depreciation and month is an optional argument that defines the number of months that assets were owned during Year 1. Returns the sum-of-years digit depreciation that results in a more accelerated depreciation than straight-line depreciation, but less than declining balance depreciation. Returns the double-declining balance depreciation that doubles the depreciation under the straight-line method and applies that accelerated rate to the original asset value minus the cumulative depreciation. The factor argument specifies the factor by which the straight-line depreciation is multiplied. If no factor is specified, a factor of 2 (for doubling) is assumed. Returns a variable declining depreciation for any specified period using any specified depreciation method, where start is the starting period of the depreciation, end is the ending period, factor is the rate at which the depreciation declines, and no_switch specifies whether to switch to the straight-line method when the depreciation falls below the estimate given by the declining balance method.
Not For Sale
EX 09 Tutorial C7483 46419.indd 515
VDB(cost, salvage, life, start, end[, factor=2] [, no_switch=FALSE])
8/23/10 12:26 PM
EX 516
Excel | Tutorial 9 Working with Financial Tools and Functions
Not For Sale
Calculating Depreciation
To calculate a straight-line depreciation, use the SLN function. To calculate a declining balance depreciation, use the DB function. To calculate a sum-of-years digit depreciation, use the SYD function. To calculate a double-declining balance depreciation, use the DDB function. To calculate a variable depreciation, use the VBD function.
REFERENCE
You can estimate straightline depreciation by using AutoFill to interpolate the linear trend using the initial value of the asset as the starting value and the salvage value as the stopping value.
With straight-line depreciation, the asset depreciates by equal amounts each year of its lifetime until it reaches the salvage value. You can calculate this annual depreciation by dividing the total depreciation of the asset by the number of years the asset is used. Based on Dianes numbers, the new workshop will depreciate $200,000 in value over 15 years, which is a straight-line depreciation of $13,333.33 per year. If you dont want to do this calculation manually, you can use the SLN function to calculate the yearly straight-line depreciation. The SLN function has the syntax
=SLN(cost,salvage,life)
where cost is the initial cost or value of the asset, salvage is the salvage value of the asset at the end of its useful life, and life is the number of years the asset will be used. To calculate the annual straight-line depreciation for Bent Cyclings new workshop, you enter the following formula, which returns $13,333.33:
=SLN(300000,100000,15)
Youll enter this function to calculate the annual depreciation of the new workshop. Youll also calculate the cumulative depreciation that will take place over the next five years, and the depreciated value of the workshop for each of the next five years.
To calculate the straight-line, cumulative, and value depreciation of the workshop:
1. In cell B10, enter the formula =SLN($B$4,$B$5,$B$6) to calculate the straight-
line depreciation for Year 1. You use absolute references so the cells wont change when you copy the formula. The formula returns $13,333, which is the annual depreciation of this asset to the nearest dollar. depreciation, the asset depreciates the same amount each year. first year.
2. Copy the formula in cell B10 to the range C10:F10. Because this is a straight-line 3. In cell B11, enter the formula =B10, which displays the depreciation for the 4. In cell C11, enter the formula =C10+B11 to calculate the cumulative depreciation
of the asset through the first two years.
EX 09 Tutorial C7483 46419.indd 516
8/23/10 12:26 PM
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
Straight-Line Depreciation
Tutorial 9 Working with Financial Tools and Functions | Excel
EX 517
5. Copy the formula in cell C11 to the range D11:F11 to calculate the cumulative
depreciation of the asset over all five of the years. At the end of Year 5, the asset will have depreciated by $66,667. to the range C12:F12 to calculate the depreciated value of the asset at the end of each of the five years by subtracting the value of the cumulative depreciation from the assets original cost.
6. In cell B12, enter the formula =$B$4B11 and then copy the formula in cell B12
7. Click cell B10 to deselect the range. Based on a straight-line depreciation, the
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
new workshops depreciated value will be $233,333 at the end of Year 5 or about 78% of its original value. See Figure 9-18.
Figure 9-18
straight-line depreciation of the workshop
conditions of the depreciation depreciated value at the end of Year 5 depreciated value at the end of Year 1
Declining Balance Depreciation
Another way to calculate depreciation is to use declining balance depreciation, in which the asset depreciates by a constant percentage each year rather than a constant amount. The depreciation value is highest early in its lifetime, so that is also when the highest declines occur. As the asset loses value, the depreciation amounts steadily decrease, though the percentage decrease remains the same. Figure 9-19 compares the straight-line and declining balance depreciation for an asset over a 15-year lifetime.
Not For Sale
EX 09 Tutorial C7483 46419.indd 517 8/23/10 12:26 PM
EX 518
Excel | Tutorial 9 Working with Financial Tools and Functions
Not For Sale
straight-line versus declining balance depreciation
initial value of the asset
Figure 9-19
salvage value
An asset depreciates more quickly initially under the declining balance model than under the straight-line model. The declining balance model is an example of a negative growth trend in which the asset depreciates in value by a constant percentage rather than by a constant amount as is the case with the straight-line model. The DB function calculates the declining balance depreciation. It has the syntax
=DB(cost,salvage,life,period[month])
where cost, salvage, and life are again the initial cost, salvage cost, and lifetime of the asset, and period is the period for which you want to calculate the depreciation. If you are calculating depreciation on a yearly basis, then the period argument would contain the year value of the depreciation. For example, to calculate the depreciation of the new workshop during the third year of its use, you could enter the following formula, which returns the value $18,383, indicating that the asset will depreciate by that amount during the third year of its use:
=DB(300000,100000,15,3)
The DB function also supports an optional month argument, which is needed when the asset is used for only part of the first year. For example, if you are depreciating an asset for only two months of the first year, you would set the value of the month parameter to 2. Diane wants you to calculate a depreciation schedule based on the declining balance assumption. Youll use the DB function to calculate the depreciation for each of the next five years.
To calculate the declining balance depreciation:
1. In cell B14, enter the formula =DB($B$4,$B$5,$B$6,B9) to calculate the declin-
ing balance depreciation for Year 1. The formula returns $21,300, the value that the new workshop will depreciate within its first year of operation. Note that you used the cell reference B9 to reference the value of the period argument.
EX 09 Tutorial C7483 46419.indd 518
8/23/10 12:26 PM
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
Tutorial 9 Working with Financial Tools and Functions | Excel
EX 519
2. Copy the formula in cell B14 to the range C14:F14 to calculate the depreciation
for each of the remaining four years. The amount of depreciation decreases each year under the declining balance schedule. ciation and depreciated value of the asset. Based on a declining balance depreciation, the depreciated value of the new workshop declines to a little more than $207,000 in value in five years. tive depreciation, and depreciated value for Years 1 through 5.
3. Copy the range B11:F12 to the range B15:F16 to calculate the cumulative depre-
4. Click cell A14 to deselect the range. Figure 9-20 shows the depreciation, cumula Cengage Learning. All rights reserved. No distribution allowed without express authorization.
Figure 9-20
declining balance depreciation
depreciated value at the end of Year 5
PROSKILLS
Decision Making: Choosing a Depreciation Schedule
How do you decide which method of depreciation is the most appropriate? The answer depends on the type of asset being depreciated. Tax laws allow different depreciation methods for different kinds of assets and different situations. In general, you want to choose the depreciation method that most accurately describes the financial status of the company. In tax statements, depreciation appears as an expense that is subtracted from the companys earnings. So, if you accelerate the depreciation of an asset in the early years of its use, you might be underestimating the companys profits, making it appear that the company is less profitable than it actually is. On the other hand, depreciating an asset slowly could make it appear that the company is more profitable than it really is. For this reason, the choice of a depreciation method is best made in consultation with a tax accountant, who is fully aware of the financial issues and the tax laws involved in depreciating an asset.
Not For Sale
EX 09 Tutorial C7483 46419.indd 519 8/23/10 12:26 PM
EX 520
Excel | Tutorial 9 Working with Financial Tools and Functions
Not For Sale
Now that you have an estimate of the depreciation of the proposed building project, you can add this information to the income statement. Diane wants a general picture of the depreciation of the proposed expansion and its impact on the companys income over the next five years. Youll enter the depreciation values in the income statement, and then calculate the operating profit for the company.
To include depreciation and operating profit in the income statement:
1. Return to the Income Statement worksheet. 2. Click cell B19, and then type = to begin the formula. You want to reference the 3. Click the Depreciation sheet tab, click cell B14, and then press the Enter key to
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
8/23/10 12:26 PM
value in cell B14 of the Depreciation worksheet, which contains the first year of depreciation that Diane would want to claim on the companys tax report.
enter the formula =Depreciation!B14. A depreciation expense of 21,300 is entered for Year 1. tion expense for each of the five years is entered in the income statement.
4. Copy cell B19, and then paste the formula into the range C19:F19. The deprecia 5. In cell B20, enter the formula =B18B19 to calculate the operating profit for the 6. Copy the formula in cell B20 to the range C20:F20 to calculate the operating
company, which is the companys initial earnings estimate reduced by the amount of the depreciation of its assets during the year. profit for each of the next five years. The projected operating profit of the company is $421,700 in Year 1 of the proposed expansion, rising to $885,446 at the end of the fifth year. See Figure 9-21.
Figure 9-21
depreciation and operating profit for Year 1 through Year 5
annual depreciation of the value of the workshop
operating profit after subtracting the cost of depreciation
Completing the Income Statement
The next part of the income statement records the companys expense from the interest payments on the $300,000 loan. You calculated these values at the end of the previous session in the Loan Schedule worksheet in the Investment Analysis workbook (shown earlier in Figure 9-10). Rather than reenter these values, you will reference the calculated values from that worksheet in the income statement. Because those values were entered as negative numbers, youll change the sign to match the format of the Income Statement worksheet.
EX 09 Tutorial C7483 46419.indd 520
Tutorial 9 Working with Financial Tools and Functions | Excel
EX 521
To reference the yearly interest payments on the proposed loan:
1. In cell B22, enter the formula =[Investment Analysis.xlsx]Loan Schedule!B36
and then press the Enter key. The value 23,923 appears in the cell.
Trouble? If an error value or a different value appears in the cell, you probably mistyped the formula. Check the formula you entered in cell B22 and correct any mistakes in spelling or the use of quotation marks.
2. Copy the formula in cell B22 to the range C22:F22. The values 19,505, 14,699, 9,471,
and 3,784 for the interest expenses in Year 2 through Year 4 appear in the cells.
Cengage Learning. All rights reserved. No distribution without allowed express authorization.
The income statement needs to include the companys taxable income, which is equal to the operating profit minus the interest payments that Diane expects to make on the proposed loan. You will estimate the pretax earnings in row 23 of the Income Statement worksheet.
To calculate the pretax earnings:
1. In cell B23, enter the formula =B20B22 to calculate the taxable income, which is
the operating profit minus the interest payments. The formula returns $397,777, which is the taxable income for the company at the end of Year 1. end of Year 5 increases to $881,662.
2. Copy the formula in cell B23 to the range C23:F23. The taxable income by the
Approximately one-third of the companys taxable income is paid toward taxes. So, for this projected income statement, youll use a 33% tax rate for each of the five years. The final row of the income statement displays the companys net income, which is the companys income after all expenses and taxes have been paid. Diane is most interested in this value because it provides a measure of the companys profitability.
To calculate the taxes and net income:
1. In cell F4, enter 33%. This is the tax rate youll use to calculate the taxes. 2. In cell B25, enter the formula =B23*$F$4 to multiply the earnings before taxes in
Year 1 by the tax rate. The estimated tax is 131,266, indicating that the company can expect to pay more than $130,000 in taxes in Year 1. the companys tax burden is estimated to be $290,948.
3. Copy the formula in cell B25 to the range C25:F25. At the end of the fifth year, 4. In cell B26, enter the formula =B23B25 to calculate the net income by subtracting the estimated tax from the earnings before taxes. The net income for Year 1 is $266,511. companys net income is projected to rise to $590,713. See Figure 9-22.
5. Copy the formula from cell B26 to the range C26:F26. At the end of Year 5, the
Not For Sale
EX 09 Tutorial C7483 46419.indd 521 8/23/10 12:26 PM
EX 522
Excel | Tutorial 9 Working with Financial Tools and Functions
Not For Sale
Final income statement projections
tax rate
Figure 9-22
projected taxes due each year projected net income
6. Save and close the workbook.
If the companys revenue grow at the rate Diane projected, and if Dianes estimate of expenses is accurate, the companys net income will increase from about $266,000 in Year 1 to more than $590,000 by the end of Year 5. Although this is good news, Diane still wonders whether the new workshop is a good financial investment and how it compares to other investments. Youll use another set of financial functions to explore this question in the next session.
EX 09 Tutorial C7483 46419.indd 522
8/23/10 12:26 PM
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
Tutorial 9 Working with Financial Tools and Functions | Excel
EX 523
Session 9.2 Quick Check REVIEW
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
EX 09 Tutorial C7483 46419.indd 523
1. The first value in a linear trend is 1000. The fifth value is 4000. What are the values of the second, third, and fourth items? 2. If the first value in a growth trend is 1000 and the fifth value is 4000, what are the values of the second, third, and fourth items? 3. The first value in a series is 1000. Extrapolate the next four values assuming a linear trend of 500. 4. If the value in a series is 1000, extrapolate the next four values assuming a growth trend of 15%. 5. A new business buys $25,000 worth of computer equipment. If the useful life of the equipment is 5 years with a salvage value of $2,000, how much will the equipment depreciate per year, assuming a straight-line depreciation? 6. Calculate how much the computer equipment described in Question 5 would depreciate in the first year assuming declining balance depreciation. 7. Calculate how much the computer equipment described in Question 5 would depreciate in the first year assuming double-declining balance depreciation with a factor of 2.
Not For Sale
8/23/10 12:26 PM
EX 524
Excel | Tutorial 9 Working with Financial Tools and Functions
Not For Sale
The payback period is the length of time before an investment recovers it s initial cost, which occurs when the values change from negative to positive.
s essi O n 9.3 v isual Overview
The Formula Auditing group includes commands to f ix formula errors and trace formulas to their root s.
The rate of return (or discount rate) is an interest rate that compares the value of current dollars to future dollars from a series of cash f lows.
The net present value (NPV) is the difference bet ween the present value of an investment and the initial expenditure on that investment.
The internal rate of return (IRR) is the return rate for a series of future cash f lows that w ill result in a net present value of zero.
The NPV func tion calculates the present value of a series of future cash f lows.
The IRR func tion calculates the internal rate of return for an investment.
EX 09 Tutorial C7483 46419.indd 524
8/23/10 12:26 PM
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
Tutorial 9 Working with Financial Tools and Functions | Excel
EX 525
n P v, i rr , and auditing
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
Tracer arrows track errors to their source by pointing from the precedent cell to the dependent cell. Formula Auditing
Tracer arrows identify precedent cells, which supply data values to the ac tive cell, and dependent cells, which depend on the value in the ac tive cell for their result.
An error value indicates that the cell contains a mistake in it s formula. The error can be in the current ce ll or in a cell that is referenced in the formula.
Red tracer arrows indicate an error in the value being received from the precedent cell or passed to the dependent cell.
This cell contains an incorrec t func tion name in the formula, which is identified by the #NAME? error value.
An error indicator, which is a green triang le in the upper-left corner of a cell, identifies a cell w ith an error or a potential error.
Not For Sale
EX 09 Tutorial C7483 46419.indd 525 8/23/10 12:26 PM
EX 526
Excel | Tutorial 9 Working with Financial Tools and Functions
Not For Sale
Calculating the Payback Period of an Investment
Diane has met with her colleagues and calculated how much additional net income will be generated by the building project compared to what the company could expect in the next five years without the new workshop. Shes placed this information in an Excel workbook.
To open and review the Building Project worksheet:
your Data Files, and then save the workbook as Return Analysis in the same folder. cell B4.
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
8/23/10 12:26 PM
1. Open the Return workbook located in the Excel9\Tutorial folder included with 2. In the Documentation worksheet, enter your name in cell B3 and the date in 3. Switch to the Building Project worksheet, and then review the predicted yearly
Figure 9-23 initial building Project worksheet
increases in net income that will be generated by the expansion. See Figure 9-23.
additional income projected to be generated by the new workshop
The predicted income increases dont tell Diane whether the initial investment is worth the total return. What if she used the $300,000 in a different way? How does the return on the companys investment from building the new workshop compare to the return it would get from investing the money elsewhere? One simple measure of the return from an investment is the payback period, which is the length of time required for an investment to recover its initial cost. For example, if the $300,000 building project brought in an extra $50,000 per year in cash, it would take six years to pay back the cost of the initial investment. Diane expects the building project will increase the companys net income by $540,000 over the next five years compared to what it would have generated had she not funded the building project. You can use the cumulative cash flow values Diane entered in the Building Project worksheet to determine when the $300,000 investment will pay for itself.
EX 09 Tutorial C7483 46419.indd 526
Tutorial 9 Working with Financial Tools and Functions | Excel
EX 527
To determine the payback period for the investment:
1. In cell B7, enter the formula =B6+A6 which returns ($290,000). After the
first year, about $290,000 still needs be recouped in extra revenue from the investment. second year, $240,000 of the initial investment remains to be paid. lative net cash flow values. See Figure 9-24.
2. In cell C7, enter the formula =C6+B7 which returns ($240,000). At the end of the 3. Copy the formula in cell C7 to the range D7:F7 to calculate the remaining cumu Cengage Learning. All rights reserved. No distribution allowed without express authorization.
Figure 9-24
Payback period for the building project
initial cost of the building project
the initial cost of the building project is paid back during Year 4
Based on these calculations, the additional revenue from building the workshop will pay back the $300,000 investment sometime during the fourth year (when the value of the cumulative net cash flow changes from negative to positive). By the end of the fifth year, the company will have shown a total profit of $240,000 from the $300,000 initial investment.
Calculating Net Present Value
The payback period is a quick method of projecting the value of an investment. The major drawback to the payback period is that it does not take into account the time value of money. To understand why, you must explore how time affects financial decisions.
The Time Value of Money
The time value of money is based on the observation that money received today is worth more than the same amount received later. One reason for this is that you can invest the money you receive today and earn interest on the investment. The time value of money can be expressed in terms of what represents a fair exchange between current dollars and future dollars. For example, is it better to get $100 today or $105 one year from now? The answer depends on what you could do with that $100 over that year. If you could invest it in an account that pays 6% interest, the $100 would turn into $106 in one year, making it better to receive the $100 now. But, if you could only earn 4% interest on the $100, it is better to receive $105 after a year. The interest rate you apply to your present funds is known as the rate of return or discount rate, and defines the time value of money by providing a way to measure future
Not For Sale
EX 09 Tutorial C7483 46419.indd 527
8/23/10 12:26 PM
EX 528
Excel | Tutorial 9 Working with Financial Tools and Functions
Not For Sale
dollars in terms of current dollars. You can use the PV (present value) function to calculate the time value of money under different rates of return. For example, to determine the present value of receiving $100 per year for the next five years at an annual rate of return of 6%, you would enter the formula
=PV(6%,5,100)
=PV(6%,5,0,500)
You can also use the FV (future value) function to estimate how much a dollar amount today is worth in future dollars. The formula =FV(5%, 2, 0, 100) returns $110.25, a positive cash flow indicating that spending $100 today with a discount rate of 5% is a fair exchange for receiving $110.25 two years from now.
which returns $373.63, indicating that it would be a fair exchange to spend $373.63 today in order to receive $500 five years from now.
Using the NPV Function
The PV function assumes that all future payments received from an investment are equal. If the future payments are not equal, you can use the NPV (net present value) function to determine what would constitute a fair exchange. The syntax of the NPV function is
=NPV(rate,value1[,value2,value3,...])
where rate is the rate of return, and value1, value2, value3, and so on are the values of future payments from the investment. The NPV function assumes that payments are paid at the end of each payment period and that the payment periods are equally spaced in time. For example, to calculate the present value of a three-year investment that pays $100 at the end of the first year, $200 at the end of the second, and $500 at the end of the third with a 6% annual rate of return, you would enter the following formula, which returns $692.15, indicating that the value of such an investment is equal to receiving $692.15 today:
=NPV(6%,100,200,500)
Unlike the PV function, which returns a negative value for the present value of the investment, the NPV function returns a positive value. This occurs because the PV function returns a cash flow value that indicates how much you need to invest now (a negative cash flow) in order to receive money later (a positive cash flow); whereas the NPV function calculates the value of those payments in todays dollars based on your chosen rate of return. The future values of an investment do need to be positive. Consider an investment that has a 6% rate of return with the following transactions: Year 1: Investor receives $250 Year 2: Investor receives $150 Year 3: Investor receives $100 Year 4: Investor pays $150 Year 5: Investor pays $400 At first glance, this seems to be a bad investment: the investor receives a total of $500 but repays $550. However, this doesnt take into account the time value of money. When the present value of this transaction is calculated using the NPV function
=NPV(6%,250,150,100,-150,-400)
EX 09 Tutorial C7483 46419.indd 528
8/23/10 12:26 PM
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
which returns -$421.24, indicating that it would be a fair exchange to spend $421.24 today in order to receive $100 per year for each of the next five years. In other words, $421.24 today is worth the same as $500 given in $100 annual payments over the next five years. For investments that pay off at the end of the investment period without any intermediate payments, you enter 0 for the pmt argument and enter the amount returned by the investment for the fv argument. For example, to calculate the present value of receiving $500 at the end of five years at a 6% rate of return, you enter the formula
Tutorial 9 Working with Financial Tools and Functions | Excel
EX 529
the formula returns $35.59, indicating that the investment is actually worth more than $35 in current dollars to the investor. This is because the returns from the investment come early, but the payments are due later, paid in dollars of lesser value.
Choosing a Rate of Return
Choosing an appropriate rate of return is related to the concept of riskthe possibility that the entire transaction will fail, resulting in a loss of the initial investment. Investments with higher risks generally should have higher rates of return. If Diane takes $300,000 and invests the money in a simple bank account (a low-risk venture), she would not expect a high rate of return. On the other hand, using the $300,000 to fund an expansion of the companys facilities with the expectation that it will result in increased income merits a higher rate of return. After discussing the issue with financial analysts, Diane has decided on a 12% rate of return. This means that Diane wants the expansion to return at least as much revenue over the next five years as she would get if she had invested $300,000 in an account that paid 12% annual interest. Youll use that rate of return in the NPV function as you calculate the net present value of the proposed expansion project.
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
To calculate the net present value of the building project:
1. In cell B10, enter 12% for the desired rate of return. 2. In cell B11, enter the formula =NPV(B10,B6:F6) to calculate the net present value
using 12% as the rate of return (cell B10) and the anticipated returns from the project (range B6:F6). The formula returns $346,483, indicating that the present value of the project is worth $346,483 in current dollars.
3. In cell B12, enter the formula =A6+B11 to combine the present value with the
initial $300,000 cash expenditure in Year 0 that pays for the project, which calculates the return from the initial investment. See Figure 9-25.
Figure 9-25
net present value of the building project
initial cost of the investment
desired rate of return net return from the building project in current dollars
present value of the building project in current dollars
Not For Sale
EX 09 Tutorial C7483 46419.indd 529 8/23/10 12:26 PM
EX 530
Excel | Tutorial 9 Working with Financial Tools and Functions
Not For Sale
With a 12% rate of return, the net present value of the $300,000 building project is more than $46,000 in current dollars. In other words, if all of Dianes assumptions are correct, investing $300,000 in the project is worth about $46,000 more than investing the same amount in an account paying 12% annual interest. The proposed expansion appears to be profitable under those terms.
Understanding Net Present Value and the NPV Function INSIGHT
The financial definition of net present value is the difference between the present value of future cash flows and the current cost of the initial investment. One source of confusion for Excel users is that despite its name, the NPV function does not return the net present value of an investment. Instead it returns the investments present value. To calculate the net present value in Excel, the cost of the initial investment must be subtracted from the value returned by the NPV function using the formula NPV value initial investment where NPV value is the value returned by the NPV function, and initial investment is the initial cost of the investment. This formula assumes that the initial investment is made immediately, and not in the future using discounted dollars. In any financial analysis, it is a good idea to test other values to see how they impact your conclusions. You will rerun your calculations using other possible rates of return.
To view the impact of different rates of return:
1. Change the value in cell B10 to 9% to decrease the desired rate of return. The net
present value in cell B12 increases to $84,810. The increase is due to the fact that the high returns in Year 4 and Year 5 are not discounted as much as they were at a 12% rate of return. net present value drops to $13,144.
2. Change the value in cell B10 to 15% to increase the desired rate of return. The 3. Change the value in cell B10 to 20% to further increase the desired rate of return.
The net present value drops to $33,501. for the expansion project.
4. Change the value in cell B10 back to 12%, which is Dianes desired rate of return
At higher rates of return, the net present value of the investment goes down. Thats not surprising when you realize the expansion is being compared with investments that offer higher and higher return values. Compared with an investment that offers a 20% interest rate, the net present value of the building project is negative, indicating that the project is worth less than an investment that offers 20% annual interest.
Calculating the Internal Rate of Return
Your analysis of different rates of return for the Bent Cycling building project illustrates an important principle: At some rate of return, the net present value of an investment will change from a positive value to a negative value. Figure 9-26 shows the change in net present value for a sample investment using different rates of return.
EX 09 Tutorial C7483 46419.indd 530
8/23/10 12:26 PM
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
Tutorial 9 Working with Financial Tools and Functions | Excel
EX 531
Figure 9-26
net present values for different rates of return
net present value equals 0
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
The point at which the net present value of an investment equals 0 is the internal rate of return (IRR) of the investment. The internal rate of return is another popular measure of the value of an investment because it forms a basis of comparison between one investment and another. Investments with higher internal rates of return are usually preferred to those with lower IRRs.
Using the IRR Function
The IRR function calculates the internal rate of return for an investment. The syntax of the IRR function is
=IRR(values[,guess=0.1])
where values are the cash flow values from the investment, and guess is an optional argument in which you guess the IRR value. A guess is needed for financial transactions that have several possible internal rates of return, such as investments that require the investor to make multiple future payments, rather than a single initial payment. For those types of transactions, an initial guess assists Excel in locating the final value for the IRR. Without the guess, Excel might not be able to calculate the IRR. If you dont include a guess, Excel will use an initial guess of 10% for the IRR and proceed from there to derive the answer. (Case Problem 2 explores how to use the guess argument in the IRR function and shows why it is sometimes necessary.) The list of values in the IRR function must include at least one positive cash flow and one negative cash flow, and the order of the values must reflect the order in which the payments are made and the payoffs are received. Like the NPV function, the IRR function assumes that the payments and payoffs occur at evenly spaced intervals. Unlike the NPV function, you include the initial cost of the investment in the values list.
Not For Sale
EX 09 Tutorial C7483 46419.indd 531 8/23/10 12:27 PM
EX 532
Excel | Tutorial 9 Working with Financial Tools and Functions
Not For Sale
Calculating the Value of an Investment
To calculate the net present value when the initial investment is made immediately, use the NPV function with the discount rate and the series of cash returns from the investment. Subtract the cost of the initial investment from the value returned by the NPV function. To calculate the net present value when the initial investment is made at the end of the first payment period, use the NPV function with the discount rate and the series of cash returns from the investment. Include the initial cost of the investment as the first value in the series. To calculate the internal rate of return, use the IRR function with the cost of the initial investment as the first cash flow value in the series. For investments that have several positive and negative cash flow values, include a guess to aid Excel in finding a reasonable internal rate of return value.
REFERENCE
Youll use the IRR function to calculate the internal rate of return for the building project.
To calculate the IRR for the proposed workshop:
1. In cell B13, enter the formula =IRR(A6:F6), selecting all of the cash flow values
including the initial expenditure of $300 in cell A6. The formula returns the IRR of 16.3%. See Figure 9-27.
Figure 9-27
internal rate of return for the investment
internal rate of return for the building project
2. Save and close the workbook.
EX 09 Tutorial C7483 46419.indd 532
8/23/10 12:27 PM
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
Tutorial 9 Working with Financial Tools and Functions | Excel
EX 533
PROSKILLS
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
Decision Making: Using Net Present Value and Internal Rate of Return to Compare Investments
Companies or businesses often must decide between several possible investment options. In general, they want only investments that have positive net present values or internal rates of return higher than a specified rate. In comparing two investments, companies usually want to select the investment with the higher net present value or the higher internal rate of return. If they rely on the net present value, they can receive contradictory results depending on what value is specified for the desired rate of return. For example, consider the following two returns from an initial investment of $1,000. Option 1 has a higher net present value for a desired rate of return greater than 9%, but Option 2 has a higher net present value when the rate of return is assumed to be 9% or less. Investment Option 1 Option 2 Year 1 $350 0 Year 2 $350 0 Year 3 $350 0 Year 4 $350 $1600
Using the internal rate of return instead of the net present value can also lead to contradictory results. This often occurs when an investment includes several positive and negative cash flows throughout the course of its history. So, to choose between two or more investments, its a good idea to graph the net present value for each investment against different possible rates of return. By comparing the graphs, you can reach a decision about which investment is the most profitable and under what conditions. This helps you make the best decision about which investment to select.
Exploring the XNPV and XIRR Functions
Both the NPV and IRR functions assume that the cash flows occur at evenly spaced intervals such as annual payments in which the cash receipts from an investment are returned at the end of the financial year. For cash flows that appear at unevenly spaced intervals, you use the XNPV and XIRR functions. The XNPV function syntax is
=XNPV(rate,values,dates)
where rate is the desired rate of return, values is the list of cash flows, and dates are the dates associated with each cash flow. The series of values must contain at least one positive and one negative value. The cash flow values are discounted starting after the first date in the list, with the first value not discounted at all. Figure 9-28 shows an investment in which the initial payment on September 1 of $300,000 is repaid in eight deposits spaced at irregular intervals over the next two years. The net present value of this investment is $25,694 based on a 5% rate of return.
Not For Sale
EX 09 Tutorial C7483 46419.indd 533 8/23/10 12:27 PM
EX 534
Excel | Tutorial 9 Working with Financial Tools and Functions
Not For Sale
net present value calculated over irregular time intervals
Figure 9-28
Likewise, the XIRR function calculates the internal rate of return for a series of unevenly spaced payments. The syntax of the XIRR function is
=XIRR(values,dates[,guess=0.1])
where values is the list of cash flow values, dates are the dates of each cash flow, and guess is an optional argument used to help Excel arrive at an answer. Figure 9-29 shows the internal rate of return for the transaction shown in Figure 9-28. The IRR of this investment is 11.07%. Figure 9-29 internal rate of return calculated over irregular time intervals
For Dianes proposed building project, all of the cash flow values are projected at regular intervals, at the end of the upcoming fiscal years, so you do not need to use either the XNPV or the XIRR function.
Auditing a Workbook
Diane prepared a workbook summarizing the financial implications of the proposed building project. Unfortunately, the formulas contain several errors. The workbook has several worksheets, and formulas on one worksheet are often based on values found on several other worksheets. This makes it difficult to locate the source of the errors. You can use the Excel auditing tools to locate and correct the source of the errors.
EX 09 Tutorial C7483 46419.indd 534
8/23/10 12:27 PM
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
Tutorial 9 Working with Financial Tools and Functions | Excel
EX 535
Youll open and review Dianes workbook.
To open and review the Financial Report workbook:
1. Open the Financial workbook located in the Excel9\Tutorial folder included with
your Data Files, and then save the workbook as Financial Report in the same folder.
2. In the Documentation worksheet, enter your name and the date. 3. Review the Income Statement, Cash Flow Schedule, Balance Sheet, and Summary
worksheets.
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
The Financial Report workbook contains several worksheets projecting the financial future of the company under the proposed expansion. You have already worked with the Income Statement, Loan Schedule, and Depreciation worksheets in the first two sessions of this tutorial. The Cash Flow Schedule worksheet projects the companys annual cash flow. The Balance Sheet worksheet projects the companys expected assets, liabilities, and equity. The Summary worksheet summarizes the results shown in the other worksheets. As you scroll through the workbook, you will see that many of the worksheets contain cells displaying error codes.
Tracing an Error
When Excel cannot calculate a formulas value, it displays an error code in the cell. An error code begins with the number sign (#) followed by an error name. The error name indicates the type of error. For example, the #NAME? error code indicates an unrecognized formula name or range name, possibly due to a spelling error. The error code does not specify where the mistake is located within the workbook. The error could be located in the cell displaying the error code. Or it could be in a precedent cell, which is a cell that is referenced by a formula. For example, if cell C15 contains the formula =C13+C14, then cells C13 and C14 are precedent cells for cell C15. Any errors in cell C13 or cell C14 would also be reported in cell C15. A cell can also be a dependent cell if other cells depend upon its value. In the preceding example, cell C15 is a dependent cell, depending on the values in cells C13 and C14. A cell can be both a precedent cell and a dependent cell. An error indicator (a green triangle in the upper-left corner of a cell) flags cells with an error or a potential error. For example, an error indicator might highlight a cell that contains a formula that is markedly different from adjacent cells to notify you that you might have entered an incorrect formula. Because errors will spread throughout a workbook from the precedent cells down through all of its descendents, you can use the auditing tools to trace an error back to its source. To identify the source of an error, select any cell containing an error value and locate its precedents. If any of those precedents display an error value, locate that cells precedents, and so on. Eventually, youll reach an error that has no precedents. That cell is the source of the error values in the dependent cells. After correcting the error, if other errors still exist, select another cell containing an error and repeat the process until you have removed all of the errors from the workbook.
After you select a cell with an error indicator, you can click the Error Alert button to display additional information about the possible error and determine if further action is needed.
Not For Sale
EX 09 Tutorial C7483 46419.indd 535 8/23/10 12:27 PM
EX 536
Excel | Tutorial 9 Working with Financial Tools and Functions
Not For Sale
Tracing Error Values
Select the cell containing an error value. In the Formula Auditing group on the Formulas tab, click the Error Checking button arrow, and then click Trace Error. Follow the tracer arrows to a precedent cell containing an error value. If the tracer arrow is connected to a worksheet icon, double-click the tracer arrow and open the cell references in the worksheet. Continue to trace the error value to its precedent cells until you locate a cell containing an error value that has no precedent cells with errors.
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
8/23/10 12:27 PM
REFERENCE
Figure 9-30
Cells throughout the Financial Report workbook display the #NAME? error code. Youll trace and correct the source of this error.
To trace an error value in the Financial Report workbook:
1. Switch to the Summary worksheet, and then click cell F18. Youll start tracing the
error with cell F18.
2. Click the Formulas tab on the Ribbon. 3. In the Formula Auditing group, click the Error Checking button arrow, and then
click Trace Arrow. A tracer arrow is attached to cell F18. See Figure 9-30.
error value being traced
Error Checking button arrow
tracer arrow
worksheet icon indicates that the precedent cell is located on a different worksheet
The tracer arrow provides a visual clue to the relationship between two cells by pointing from the precedent cell to the dependent cell. In this case, the tracer arrow points from a worksheet icon to cell F18. The worksheet icon indicates that the precedent for the active cell is in another worksheet. Youll jump to that cell.
To continue tracing the error to its source:
1. Double-click the tracer arrow that connects the worksheet icon to cell F18. The
Go To dialog box opens, listing the reference to cell G19 in the Balance Sheet worksheet.
EX 09 Tutorial C7483 46419.indd 536
Tutorial 9 Working with Financial Tools and Functions | Excel
EX 537
2. In the Go to box, click the cell reference, and then click the OK button. Cell G19 in 3. In the Formula Auditing group on the Formulas tab, click the Error Checking
the Balance Sheet worksheet is now the active cell. Notice that the #NAME? errors appear throughout this worksheet, too. button arrow, and then click Trace Error. The tracer arrows go through several cells, once again stopping at a worksheet icon. See Figure 9-31.
Figure 9-31
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
error values traced across the worksheet
dependent cell
following the tracer arrows backward leads to the worksheet icon
The tracer arrows provide a visual picture of how the formulas in the Balance Sheet worksheet are interconnected. The arrows show that cell G19 is dependent on the value in cell G17; cell G17 depends on the value in cell G15, which is dependent on cell F15 and then cells E15, D15, and C15. The value in cell C15 is dependent on the values in cell B15 and a cell in another worksheet. Red tracer arrows indicate that an error has been received from the precedent cell or passed to the dependent cell. A blue tracer arrow indicates that no error has been received or passed.
To locate the source of the error:
1. Double-click the tracer arrow that connects the worksheet icon to cell C15. The
Go To dialog box opens.
2. Click the reference to cell B27 in the Income Statement worksheet, and then click
the OK button. Cell B27 in the Income Statement worksheet becomes active. button arrow, and then click Trace Error to trace the source of the error in cell B17. As shown in Figure 9-32, the source of this error ends at cell B17.
3. In the Formula Auditing group on the Formulas tab, click the Error Checking 4. Click cell B17 if necessary, and then review the formula in the formula bar. The
function name in the formula was entered incorrectly as SUMM, which is why the #NAME? error code is displayed in the cell.
Not For Sale
EX 09 Tutorial C7483 46419.indd 537 8/23/10 12:27 PM
EX 538
Excel | Tutorial 9 Working with Financial Tools and Functions
Not For Sale
source of the error value
Figure 9-32
function name is misspelled this error has no precedent cells containing errors
5. Change the value in cell B17 to =SUM(B11:B16) and then press the Enter key.
Tracer arrows disappear when you correct the error source, insert or delete columns and rows, and delete or move cells. To restore the tracer arrows, retrace the formulas in the workbook.
When you change the formula, the #NAME? error codes disappear from the worksheet. Also, the color of the tracer arrows changes from red to blue because they no longer connect to cells containing errors. button to remove all of the tracer arrows from the worksheet.
6. In the Formula Auditing group on the Formulas tab, click the Remove Arrows
Trouble? If the tracer arrows disappeared from your workbook, Excel removed them automatically. Continue with Step 7.
7. Verify that the #NAME? error values disappeared from all of the cells in the workbook, and then switch to the Summary worksheet.
You have located the source of the #NAME? error. Note that you can use the auditing tools to track any cell formula whether it contains an error or not. To trace the precedents of the active cell, click the Trace Precedents button in the Formula Auditing group on the Formulas tab. If you want to locate the cells that are dependent upon the active cell, click the Trace Dependents button.
Evaluating a Formula
One drawback to using tracer arrows is that they can clutter a worksheet. Sometimes, you want to trace only a single formula to its roots. Another way to explore the relationship between cells in the workbook is by evaluating formulas. From the Evaluate Formula dialog box, you can display the value of different parts of the formula or drill down through the cell references in the formula to discover the source of the formulas value.
EX 09 Tutorial C7483 46419.indd 538
8/23/10 12:27 PM
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
Tutorial 9 Working with Financial Tools and Functions | Excel
EX 539
In the Summary worksheet, cells F17 and F18 show Bent Cyclings total assets and total liabilities and equity in Year 5, which were calculated in the Balance Sheet worksheet. On a balance sheet, the total assets value should equal the total liabilities and equity value. Checking that these totals match is a basic step in auditing any financial report. In this case, the total assets value in cell F17 is $2,460,883, but the total liabilities and equity in cell F18 is $2,409,557. Because the values differ, an error must occur somewhere in the workbook. Although you could use the tracer arrows to trace the precedents of cells F17 and F18, youll instead evaluate the formula in cell F17 of the Summary worksheet to locate the error.
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
To evaluate the formula in cell F17 of the Summary worksheet:
1. Click cell F17. Youll evaluate the formula in this cell, which returns the total
assets on the balance sheet.
2. In the Formula Auditing group on the Formulas tab, click the Evaluate Formula
button. The Evaluate Formula dialog box opens. The formula in cell F17 is displayed with any cell references underlinedcell G11 of the Balance Sheet worksheet, in this case. See Figure 9-33.
Figure 9-33
evaluate Formula dialog box
formula in cell F17 steps into the cell reference, displaying the formula in the underlined cell displays the value of the underlined section of the formula
3. Click the Step In button to display the formula contained in the cell reference.
Cell G11 in the Balance Sheet worksheet becomes the active cell and worksheet. The dialog box displays the formula in cell G11: =G7+G9. Cell G7, which contains the total current assets for the company in Year 5, is underlined. Formula dialog box displays 2201970.4929975+G9. Cell G9, which contains the depreciated value of the companys plant and equipment for Year 5, is underlined.
4. Click the Evaluate button to display the value stored in cell G7. The Evaluate
5. Click the Step In button. The dialog box displays the formula =Depreciation!C12,
indicating that the depreciated value of the companys plant and equipment in Year 5 comes from cell C12 in the Depreciation worksheet.
6. Click the Step In button. Cell C12 in the Depreciation worksheet becomes active.
See Figure 9-34.
Not For Sale
EX 09 Tutorial C7483 46419.indd 539 8/23/10 12:27 PM
EX 540
Excel | Tutorial 9 Working with Financial Tools and Functions
Not For Sale
drilling down through a formula
Figure 9-34
depreciated value comes from Year 2, not Year 5
Diane tells you that the depreciated value should be based on the Year 5 value in cellF12, not the Year 2 value in cell C12. This is probably the source of the error. Youll correct the formula in the Balance Sheet worksheet.
To correct the error in cell G9 of the Balance Sheet worksheet:
1. Click the Step Out button to move back up in the hierarchy of formulas, returning
to cell G9 in the Balance Sheet worksheet.
2. Click the Close button to close the Evaluate Formula dialog box. 3. In the Balance Sheet worksheet, change the formula in cell G9 to
=Depreciation!F12.
4. Switch to the Summary worksheet, and then confirm that the values in cells F17
and F18 match. The Year 5 values for assets, liabilities, and equity are in balance.
Using the Watch Window
In a workbook that involves dozens of worksheets with interconnected formulas, you may often want to be able to view the impact of changing a value in one worksheet on cell values in other worksheets. Moving back and forth between worksheets can be time consuming and clumsy if the workbook contains many worksheets and the values you want to follow are spread across several of them. Rather than jumping between different worksheets, you can create a watch window, a dialog box that displays values of cells located throughout the workbook, allowing the user to view the impact of changing a cells value on widely scattered dependent cells. Diane wants to look at some of the underlying assumptions in the building project. When you worked on the income statement, you assumed a 33% income tax rate. Diane wants to know what would happen to the building proposal if the government increased taxes in the next five years. Youll create a Watch Window that displays the net present value and internal rate of return as you edit the income tax rate value.
EX 09 Tutorial C7483 46419.indd 540
8/23/10 12:27 PM
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
Tutorial 9 Working with Financial Tools and Functions | Excel
EX 541
To use the Watch Window to display cell values on the Summary worksheet:
1. Switch to the Income Statement worksheet. 2. In the Formula Auditing group on the Formulas tab, click the Watch Window button. The Watch Window dialog box opens.
3. Click the Add Watch button to open the Add Watch dialog box, click the
Summary sheet tab, click cell F9 in the Summary worksheet, and then click the Add button. The information in cell F9 is added to the Watch Window. Summary worksheet, and then click the Add button. The information in cell F14 is added to the Watch Window.
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
4. Click the Add Watch button, click the Summary sheet tab, click cell F14 in the
5. Click the Add Watch button, click the Summary sheet tab, click cell F17 in the
Summary worksheet, and then click the Add button. The Watch Window displays the net income, net cash flow, and total asset values for Year 5, which are stored in the Summary worksheet. As you change values in the workbook, you can view the impact of those changes in the Watch Window. 33% to 39%. In the Watch Window, you instantly see that increasing the tax rate to 39% decreases the net income at the end of Year 5 to $537,813, drops the net cash flow for that year to $483,186, and reduces the total assets of the company to $2,221,537. See Figure 9-35.
6. In cell F5 of the Income Statement worksheet, change the assumed tax rate from
Figure 9-35
watch window
Watch Window
revised tax rate
Year 5 net income Year 5 net cash flow Year 5 total assets
7. Change the assumed tax rate value in cell F5 back to 33%, and then close the
Watch Window dialog box.
8. Save the workbook, and then close it.
Not For Sale
EX 09 Tutorial C7483 46419.indd 541 8/23/10 12:27 PM
EX 542
Excel | Tutorial 9 Working with Financial Tools and Functions
Not For Sale
You report to Diane that if income taxes increase six percentage points to 39%, the net income for the company at the end of Year 5 will drop by more than $50,000. A similar drop will be seen in the companys net cash flow. Diane will continue to use the Excel financial tools to project the companys financial future and evaluate different building plans and proposals.
Session 9.3 Quick Check REVIEW
1. Why is the payback period not always appropriate for determining the value of an investment? 2. Suppose the rate of return is 5%. Is $95 today worth more, less, or the same as $100 a year from now? 3. Suppose you receive the following payments: $50 a year beginning now, $75 in two years, and $100 in three years. What is the present value of this investment? Assume a 6% rate of return. 4. Suppose you spend $350 on an investment that pays $75 a year for the next six years. If you make the payment immediately, what is the net present value of the investment? Assume a 6% rate of return. 5. Suppose that instead of spending $350 immediately on an investment, you spend $350 a year from now and then receive $75 a year for the next six years after that. What is the net present value assuming a 6% rate of return? 6. Calculate the internal rate of return for the investment in Question 4. If another investment is available that pays a 7.3% rate of return, should you take it? 7. What do red tracer arrows suggest? 8. A cell displays the #REF! error code. Suggest a possible source of this error.
EX 09 Tutorial C7483 46419.indd 542
8/23/10 12:27 PM
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
Tutorial 9 Working with Financial Tools and Functions | Excel
EX 543
P R ACT ICE
Practice the skills you learned in the tutorial using the same case scenario.
Review Assignments
data Files needed for the review assignments: building.xlsx, loan.xlsx, nvP.xlsx, and statement.xlsx Diane is considering a different building proposal for Bent Cycling. Rather than building a new workshop and design facility, the company will substantially upgrade its existing facilities. The cost for remodeling is estimated to be $200,000. Diane wants you to analyze the financial aspects of the proposed remodeling, taking into account the cost of the loan and the yearly depreciation of the new equipment. Youll add the appropriate financial functions and formulas to the workbooks Diane created, correcting any errors you find. Complete the following: 1. Open the building, loan, nPv, and statement workbooks located in the Excel9\Review folder, and then save them in the same folder as building Project, loan schedule, nPv and irr, and income statement, respectively. In the Documentation worksheet of each workbook, enter your name and the date. 2. Go to the Loan Schedule workbook, and then switch to the Loan Analysis worksheet. To pay for the remodeling, the company will take out a five-year loan for $200,000 at 7.5% interest, compounded monthly. a. In the range A5:F5, enter the conditions of the loan. Use formulas to calculate the rate per month and the number of payment periods in the loan. b. In cell G5, use the PMT function to calculate the total monthly payment on the loan based on the loan conditions already entered into the worksheet. Assume the loan will be completely paid off at the end of the last period. 3. In the range C17:F76, complete the amortization schedule. Use the IPMT function in column D to calculate the interest payment due per month. Use the PPMT function in column E to calculate how much of each monthly payment is used to pay off the principal. Display the total monthly payment in column F. Column C displays the remaining principal at the start of each month. The initial principal remaining is $200,000. The subsequent remaining principal values should be reduced by the principal payment made in the previous month. 4. In the range B11:G13, complete the yearly payment calculations on both the interest and principal, displaying how much the company will pay in interest each year and overall, and how much of the yearly payments will be used to reduce the principal. In row 11 use the CUMIPMT function to calculate the cumulative interest payments per year, and in row 12 use the CUMPRINC function to calculate the total payments made toward the principal. Save the Loan Schedule workbook. 5. Go to the Income Statement workbook. The Income Statement worksheet contains a five-year projection of the income statements for the company. Project the revenue and extrapolate the expense values for the company. a. In the range B8: F8, project the revenue in Years 2 through 4 by interpolating the increase in revenue between Year 1 ($1,800,000) and Year 5 ($3,600,000) assuming a growth trend. b. Extrapolate the expense values in the range B12:B17 through the range C12:F17 assuming a growth trend in which expenses rise by 12% per year. 6. Go to the Depreciation worksheet and complete the depreciation schedule. a. In the range B4:B6, enter the conditions of the depreciation based on Dianes estimates that the new equipment and features of the remodeled workshop will depreciate from $200,000 to $50,000 in 10 years. b. In the range B10:F10, calculate the yearly depreciation of the asset assuming a straight-line depreciation using the SLN function. c. In the range B14:F14, calculate the cumulative depreciation using the DB function.
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
Not For Sale
EX 09 Tutorial C7483 46419.indd 543
8/23/10 12:27 PM
EX 544
Excel | Tutorial 9 Working with Financial Tools and Functions
Not For Sale
7. 8. d. In the range B12:F12, calculate the assets depreciated value each year. e. In the range B14:F16, calculate the yearly depreciation of the asset assuming a declining balance depreciation, the cumulative depreciation, and the assets depreciated value each year. In the Income Statement worksheet, apply the yearly depreciation you calculated in the Depreciation worksheet under the declining balance schedule to the rangeB21:F21. In the range B24:F24, reference the interest expense values you calculated for each year from the Loan Schedule workbook in the Loan Analysis worksheet in the B11:F11 range. Save and close the Income Statement workbook and close the Loan Schedule workbook. Go to the NPV and IRR workbook. In the Return on Investment worksheet, analyze the return on the remodeling project. a. The range B6:F6 contains the additional cash receipts predicted to be generated from the building project. In the range B7:F7, calculate the cumulative net cash flow from these receipts. In what year would the building project pay for itself? b. Compare this investment to what Diane would receive from an investment returning 10% annual interest. In cell B10, enter 10%. In cell B11, calculate the net present value of the remodeling investment. How does it compare in current dollars to what Diane could have received by investing the $200,000 in an account paying 10% annual interest? c. In cell B12, calculate the internal rate of return for this investment. Save and close the NPV and IRR workbook. Go to the Building Project workbook. Several cells display the #DIV/0! error value. Starting with cell E5 in the Balance Sheet worksheet, trace the error to its source and then correct it. Save and close the Building Project workbook. Submit the finished workbooks to your instructor, either in printed or electronic form, as requested.
10.
11.
Use the skills you learned to calculate the return from investing in realestate.
Case Problem 1
data File needed for this case Problem: condo.xlsx E-Park Real Estate Greg Baer is the owner of E-Park Real Estate, a small real-estate company in Estes Park, Colorado. Greg is examining the finances involved with purchasing a large condo outside of the village. E-Park Real Estate will hold on to the condominium for the next 10 years, receiving yearly income from rentals that will cover the expenses and will eventually pay back the cost of the initial investment. Greg wants you to calculate the return from this investment. He already created a worksheet containing the relevant financial data. You need to complete the worksheet by adding the formulas to project the value of the investment over the next 10 years. Complete the following: 1. Open the condo workbook located in the Excel9\Case1 folder, and then save the workbook as condo investment in the same folder. In the Documentation sheet, enter your name in cell B3 and the date in cell B4. The Investment Analysis worksheet contains a projected income statement and a cash flow statement. Greg entered the initial conditions of the investment. The cost of the condo is $325,000. For tax purposes, Greg plans to depreciate the condo completely over the 10-year period of the investment. At the end of the 10-year period, he plans to sell the condo for $425,000. Greg assumes a 34% tax rate on rental income and also on the income resulting from the sale of the condo in Year 10. You need to complete the worksheet by adding the formulas to project the value of the investment over the next 10 years.
EX 09 Tutorial C7483 46419.indd 544
8/23/10 12:27 PM
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
9.
APPLY
Tutorial 9 Working with Financial Tools and Functions | Excel
EX 545
2. In the range C11:L11, enter the yearly rental income for the condo, assuming that the income grows at a linear trend from $42,000 in Year 1 to $56,000 in Year 10. In cell L12, enter a reference to the sales value of the condo already entered in cell B7. In the range C13:L13, calculate the total revenue generated by the rental and sale of the condo from Year 1 through Year 10. 3. In the range C17:L17, insert the annual property tax paid on the condo, assuming that $3,000 is paid in Year 1 and the tax increases following a linear trend to a value of $5,500 in Year 10. In the range C18:L18, insert the annual miscellaneous expenses, starting from a Year 1 value of $4,500 and assuming that expenses increase following a linear trend to a Year 10 value of $9,000. In the range C19:L19, calculate the total expenses for each year. 4. In the range C21:L21, calculate the initial earnings estimate by subtracting the total yearly expenses from the total yearly revenue. 5. In the range C22:L22, calculate the annual depreciation of the condo from Year 1 to Year 10. Assume a straight-line depreciation and use the initial cost, useful life, and salvage values entered at the top of the worksheet. 6. In the range C23:L23, calculate the taxable income by subtracting the yearly depreciation from the yearly initial earnings estimate. 7. In the range C25:L25, calculate the tax due on the taxable income for each of the next 10 years by multiplying the taxable income by the tax rate in cell B8. 8. In the range C27:L27, calculate the net income for each of the 10 years of the investment. The net income is equal to the taxable income minus the total tax due. 9. The cash flow schedule at the bottom of the worksheet calculates the yearly cash receipts that Greg expects to receive from owning the condo. Greg already entered all of the formulas to generate the cash flow schedule. The range B35:L35 contains the cumulative cash flow from the condo through the 10 years that Greg will own it. Based on his financial projections, how long will Greg have to wait until the yearly cash receipts generated from the condo cover the original purchase price? 10. In cell F4, enter 8% because Greg wants his investment to have at least an 8% rate of return. 11. In cell F5, calculate the net present value of the investment using the cash flow values in the cell range C33:L33 as the yearly returns from the condo investment, and using the value in cell B4 as the initial cost of the investment. Assume that the initial expenditure on the condo occurs immediately. 12. In cell F6, calculate the internal rate of return on the investment using the net cash flow values from the range B33:L33. Based on your analysis, will purchasing the condo provide a greater return than a different investment offer with 8% annual interest? If the tax rate increases from 34% to 39%, will this still be the case? 13. Save and close the workbook. Submit the finished workbook to your instructor, either in printed or electronic form, as requested.
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
APPLY
Calculate the net present value of a proposed college savingsplan.
Case Problem 2
data File needed for this case Problem: college.xlsx Eason Financial Services Jesse Buchmann is a finance officer at Eason Financial Service. Shes working with Andrew and Beth Ferris in developing a savings plan for their daughter Tyler, who is 11. The couples goal is to save enough money over the next eight years to fund Tylers four years of college. They plan to make annual deposits into their savings account starting immediately and through Tylers four years of college. When Tyler begins school in eight years, theyll withdraw money at the beginning of each term to cover much of the cost of tuition, room, and board. They have several different options
Not For Sale
EX 09 Tutorial C7483 46419.indd 545
8/23/10 12:27 PM
EX 546
Excel | Tutorial 9 Working with Financial Tools and Functions
Not For Sale
for the amount to deposit. Jesse has asked you to help her set up an Excel workbook to determine whether different savings plans will cover the cost of Tylers college education. Complete the following: 1. Open the college workbook located in the Excel9\Case2 folder and save the workbook as college savings Plan in the same folder. In the Documentation worksheet, enter your name in cell B3 and the date in cell B4. 2. Go to the Calculator worksheet. The couple is thinking about depositing their funds in an account that pays 6% annual interest. They would like to deposit $6,500 at the beginning of each year. Also, Tylers grandmother wants to help by giving Tyler an extra $100 for her birthday on March 15, and Christmas each year up until she starts college. Enter these values as positive cash flows in the range B4:B6 on the worksheet. 3. Tyler starts college in eight years. The current cost to attend the local university is $15,000 per year. Andrew and Beth believe that college costs will increase at an annual rate of 4% per year. Enter these assumptions into the range B9:B11 of the worksheet. 4. You can specify the dates on which the couple will make deposits in the savings plan. They will make the $6,500 deposit at the beginning of each year on January 10. Tylers birthday is March 15 and Christmas is December 25. Using the values you specified in the savings plan, enter the amount of the deposits into the savings account for each of the 12 years into the range G4:G33. 5. Assuming that the term begins on August 15 each year, calculate the future value of the amount of money the couple will withdraw in each of the four years to pay for Tylers education. Place your estimates into cells G28, G30, G32, and G34 as negative cash flows. (Hint: Use the FV function along with the current cost of education and the estimated inflation rate to determine the future annual cost of education for the couple.) 6. Calculate the projected cost of four years of Tylers education in cell B12. 7. Because the deposits and withdrawals do not occur at evenly spaced intervals, use the XNPV function to calculate the net present value of the savings plan, displaying your results in cell B15. 8. If the net present value of the savings plan is positive or zero, it will cover the cost of Tylers education. If the net present value is negative, the plan is underfunded and will not cover the cost. Insert an IF function into B16 that displays Yes if the plan will cover the projected costs and No if it will not. 9. Apply conditional formatting to cell B16 that displays the text in a green font on a light green background if the cells value is Yes, and red text on a light red background if the cells value is No. 10. Based on estimates, does the proposed plan cover the projected cost of Tylers education? Redo your analysis under the following assumptions: college inflation current college cost annual deposit 6% $15,000 $6,500 6% $15,000 $7,500 6% $15,000 $8,000 7% $12,000 $8,000 7% $13,000 $8,000 11. Save and close the workbook. Submit the finished workbook and your answers to your instructor, either in printed or electronic form, as requested.
EX 09 Tutorial C7483 46419.indd 546
8/23/10 12:27 PM
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
Tutorial 9 Working with Financial Tools and Functions | Excel
EX 547
CHALLENGE
Explore the problem of multiple IRR values in investigating the profitability of a proposed limestone quarry.
Case Problem 3
data File needed for the case Problem: witte.xlsx Witte Limestone Sheila Dawson is a manager at Witte Limestone, a firm specializing in the excavation and processing of limestone. Sheila is currently working on a proposal for excavating a new limestone quarry outside of the town of New Berlin, Wisconsin. According to the most recent estimates, the area has enough limestone to support a quarry for the next 20 years. There will be a substantial startup cost as well as substantial costs at the end of the quarrys useful lifetime. The quarry will be most profitable in its early and middle stages of development, but will lose profitability in its later stages as it becomes more difficult to extract the remaining limestone deposits. Also, current environmental regulations require the company to restore the area to its original condition when the quarry work is finished. Sheila asks you to determine the profitability of the investment in the New Berlin quarry. Complete the following: 1. Open the witte workbook located in the Excel9\Case3 folder, and then save the workbook as witte limestone in the same folder. In the Documentation worksheet, enter your name and the date. 2. The initial cost to set up the quarry is $3,800,000. Enter this value as a negative cash flow in cell B5 of the Investment Analysis worksheet. 3. Sheila estimates the quarry will generate $170,000 in cash during its first year of operation, with the yearly cash flow increasing to $980,000 by the end of Year 5. Interpolate the Year 1 through Year 5 cash flow values, assuming that the increase in cash follows a growth trend. 4. From Year 5 to Year 10, Sheila estimates the yearly cash flow will increase from $980,000 to $1,100,000. Interpolate the cash flow figures, assuming a linear trend in the increase in cash. From Year 10 to Year 15, Sheila estimates the yearly cash flow will decrease from $1,100,000 to $850,000. Interpolate the yearly cash flow figures once again assuming a linear trend in the decline. 5. From Year 15 to Year 20, the yearly cash flow from the quarry will decline from $850,000 to $50,000. Interpolate the yearly cash flow figures assuming a growth trend in the decline in net cash. 6. In Year 21, the quarry will close and the company will spend an estimated $12,000,000 to restore the area to its original pristine condition. Enter this value in cell B26 as a negative cash flow. 7. Calculate the total cash flow of the project by entering total in cell A27 and the sum of the values in the range B5:B26 in cell B27. Format the range A27:B27 with the Total cell style. Based on this estimate, will the quarry pay back the cost of the initial investment and the environmental cleanup? 8. Create a chart of the net cash flow values from the range A6:B25 using the Scatter with Straight Lines chart type. Place the new chart on a chart sheet named cash Flow chart. Remove the legend from the chart. Add the chart title Yearly cash returns from the new berlin Quarry above the chart. Add the title Year to the horizontal axis and the title net cash Flow per Year to the vertical axis. 9. The company wants at least an 11% rate of return on this investment. In the Investment Analysis worksheet, estimate the internal rate of return for the entire investment from Year 0 through Year 21 by first inserting guesses on the IRR in cells D5 and D6. Enter the value 1% in cell D5 and the value 10% in cell D6. In cell E5, calculate the IRR of the investment, using the guess from cell D5. In cell E6, calculate the IRR of the investment using the guess from cell D6. Format the calculated IRRs to show two decimal places. Are the two IRR values consistent? Based on their results, can you confirm that the investment will have a return rate high enough for the company to proceed?
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
Not For Sale
EX 09 Tutorial C7483 46419.indd 547
8/23/10 12:27 PM
EX 548
Excel | Tutorial 9 Working with Financial Tools and Functions
Not For Sale
10. Calculate the net present value of the quarry project for different discount rates. Enter the values 1% through 20% in 1% increments into the range D9:D28. In the range E9:E28, calculate the net present value of the investment assuming the discount rates in column D and assuming that the initial investment in the quarry will occur immediately. For what discount rates is the NPV positive? For what discount rates is the NPV negative? Based on your calculations, can you determine whether the investment will be worthwhile if the desired rate of return is 11%? Compare your answer with your answer in Step 9. What accounts for the apparent discrepancy between the two answers? 11. Create a chart of the net present values from the range D9:E28 using the Scatter with Smooth Lines chart type. Place the new chart on a chart sheet named nPv chart. Remove the legend from the chart. Add the chart title net Present values above the chart. Add the title rate of return to the horizontal axis and the title net Present value to the vertical axis. Recall that the IRR value is the rate of return in which the net present value is equal to 0. Does your chart explain your results in Step 9? 12. The startup date for the quarry might be delayed because of local community action regarding environmental concerns about the quarry. Recalculate the net present values for each of the discount rates in D9:D28 by inserting new net present value calculations into the range F9:F28, assuming that the initial investment occurs not immediately but after a years delay. Assuming a discount rate of 11%, how much will the delay cost the company in current dollars? 13. Save and close the workbook. Submit the finished workbook to your instructor, either in printed or electronic form, as requested.
CRE ATE
Create a workbook to analyze the profitability of a new car wash.
Case Problem 4
data File needed for this case Problem: blue.xlsx Blue Marlin Gas Yasmin Arizmendi is the business manager for Blue Marlin Gas, a gas station and convenience store in Hartford, Michigan. Yasmin is thinking about making some capital improvements, including adding a drive-through car wash to the station. He wants to perform an investment analysis to determine whether the capital improvement will pay off for the company. He has asked for your help in creating the workbook to determine what kind of return he can expect on his investment. Complete the following: 1. Open the blue workbook located in the Excel9\Case4 folder, and then save the workbook as blue marlin in the same folder. In the Documentation worksheet, enter your name and the date. 2. Create a worksheet named investment analysis to analyze Yasmins investment in the new car wash. 3. The initial cost to build a drive-through car wash is $350,000. Yasmin determines that the useful life of the equipment is 15 years with a salvage value of $50,000. Enter this information into the Investment Analysis worksheet. 4. Create an annual income statement table to project the income from the car wash from Year 0 (the current year) through Year 15. For each year, calculate the income from the car wash, the expense of building and operating the car wash, the taxable income from the car wash, the taxes due on the car wash income, and the net after tax income from the car wash. 5. Yasmin expects the car wash to generate $0 of cash in Year 0, $70,000 in Year 1, and $110,000 in Year 15. Calculate the income values in Year 2 through Year 14 assuming a linear trend.
EX 09 Tutorial C7483 46419.indd 548
8/23/10 12:27 PM
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
Tutorial 9 Working with Financial Tools and Functions | Excel
EX 549
6. The expenses should be broken down into two subcategories: the cost of building the car wash and the cost of operating the car wash. Set the Year 0 cost of building the car wash to $350,000, and set this cost to $0 for Year 1 through Year 15. Set the expense of operating the car wash to $0 in Year 0, $25,000 in Year 1, and $50,000 in Year 15. Calculate the Year 2 through Year 14 values assuming a linear trend in the expense of running the car wash. All expense values should be entered as negative cash flows. Calculate the total expenses incurred by the car wash in each year. 7. Calculate the initial earnings from the car wash for Year 0 through Year 15, equal to the revenue generated from the car wash minus the expense. 8. The depreciation of the car wash equipment is $0 in Year 0. In Year 1 through Year 15, calculate the amount that the equipment depreciates in value each year under a declining balance depreciation model. 9. Calculate the taxable income from the car wash for Year 0 to Year 15, equal to the initial earnings reduced by the depreciation of the equipment. 10. Assuming a 37% tax rate on the taxable income, calculate the amount of taxes owed for each year from the car wash operation, except for Year 0 in which no taxes aredue. 11. Calculate the net income from the car wash for Year 0 through Year 15, equal to the taxable income reduced by the amount of taxes owed. Below the annual income statement table, insert another table to calculate the annual cash flow from running the car wash. The table should include the net cash inflow from operating the car wash in Year 0 through Year 15, and the net cash outflow from operating the car wash in Year 0 through Year 15. 12. Calculate the Year 0 through Year 15 cash flow from the new car wash. The cash inflow is equal to the taxable income added back to the depreciation value you calculated earlier. The Year 0 through Year 15 cash outflow will only consist of the taxes that you calculated in Step 10. 13. Calculate the net cash flow for Year 0 through Year 15 by reducing the net cash inflow by the net cash outflow. 14. Using the yearly net cash flow figures you calculated in Step 14, calculate the net present value of the investment in the new car wash over its projected 15 years of operation. Yasmin wants the investment to show at least a 7% rate of return. Assume that the initial investment of $350,000 in building the car wash is made immediately. Under those conditions, is the proposed car wash profitable in terms of its net present value? 15. Calculate the internal rate of return for the investment. 16. Summarize your findings, indicating whether the investment in the car wash will generate the return Yasmin is looking for. 17. Save and close the workbook. Submit the finished workbook to your instructor, either in printed or electronic form, as requested.
Cengage Learning. All rights reserved. No distribution allowed without express authorization.
SAM: Skills Assessment Manager
A SSESS
EX 09 Tutorial C7483 46419.indd 549
For current SAM information, including versions and content details, visit SAM Central (http://samcentral.course.com). If you have a SAM user profile, you may have access to hands-on instruction, practice, and assessment of the skills covered in this tutorial. Because various versions of SAM are supported throughout the life of this text, check with your instructor for the correct instructions and URL/Web site for accessing assignments.
Not For Sale
8/23/10 12:27 PM
EX 550
Excel | Tutorial 9 Working with Financial Tools and Functions
Not For Sale
e n D ing DaTa File S
Excel9 Tutorial Review
Financial Report.xlsx Income Projection.xlsx Investment Analysis.xlsx Return Analysis.xlsx
Case1
Building Project.xlsx Income Statement.xlsx Loan Schedule.xlsx NPV and IRR.xlsx
Case2
Condo Investment.xlsx
Case3
College Savings Plan.xlsx
Case4
Witte Limestone.xlsx
Blue Marlin.xlsx
EX 09 Tutorial C7483 46419.indd 550
8/23/10 12:27 PM
Cengage Learning. All rights reserved. No distribution allowed without express authorization.