Spreadsheets got their start in the accounting and finance departments back when it was all done with paper and pencil. And even though Excel has grown far beyond a simple electronic ledger sheet, it's still a required tool in business.

In this chapter, you'll look at some formulas commonly used in accounting, finance, and other areas of businesses.

Performing Common Business Calculations

This section provides a reference for some of the more common business- and financial-oriented formulas that you may be asked to create when working as a business analyst using Excel.

Calculating gross profit margin and gross profit margin percent

Gross margin is the money left over after subtracting the cost of goods sold from the revenue. It's the amount of sales that the business uses to cover overhead and other indirect costs. To compute the gross margin, simply subtract the cost of goods sold from the revenue. For gross margin percent, divide the gross margin by revenue. Figure 15.1 shows the financial statements of a manufacturing company. Gross margin is shown in cell C5, and gross margin percent is shown in cell D5.

Gross Margin: =C3-C4
Gross Margin Percent: =C5/$C$3 

The gross margin formula simply subtracts cell C4 from cell C3. The gross margin percent divides C5 by C3, but note that the C3 reference is absolute because it has dollar signs. This allows you to copy the formula to other lines on the income statement to see the percentage of revenue, a common analysis performed on income statements.

Calculating markup

Markup is often confused with gross margin percent, but they are different. Markup is the percentage added to costs to arrive at a selling price. Figure 15.2 shows the sale of a single product, the markup applied, and the gross margin realized when sold.

The markup is computed by dividing the selling price by the cost and subtracting 1.

=(C3/C2)-1 

By marking up the cost of the product 32%, you achieve a 24% gross margin. If you want to mark up a product to get a 32% margin (as shown in column E of Figure 15.2), use the following formula:

=1/(1-E9)-1 

Using this formula, you would need to mark up this product 47% if you want your income statement to show a 32% gross margin.

Calculating EBIT and EBITDA

Earnings before interest and taxes (EBIT) and earnings before interest, taxes, depreciation, and amortization (EBITDA) are common calculations for evaluating the results of a business. Both are computed by adding back certain expenses to earnings, also known as net profit.

Figure 15.3 shows an income statement and the results of the EBIT and EBITDA calculations below it.

EBIT

• =C18+VLOOKUP("Interest Expense",$B$2:$C$18,2,FALSE)+VLOOKUP("Income Tax Expense",$B$2:$C$18,2,FALSE) 

EBITDA

• =C20+VLOOKUP("Depreciation Expense",$B$2:$C$18,2,FALSE)+VLOOKUP ("Amortization Expense",$B$2:$C$18,2,FALSE) 

The EBIT formula starts with net loss in C18 and uses two VLOOKUP functions to find the interest expense and income tax expense from the income statement. For EBITDA, the formula starts with the result of the EBIT calculation and uses the same VLOOKUP technique to add back the depreciation expense and amortization expense.

There is a benefit to using VLOOKUP rather than simply using the cell references to those expenses. If the lines on the income statement are moved around, the EBIT and EBITDA formulas won't need to be changed.

See Chapter 14, “Using Formulas for Matching and Lookups,” for more on the VLOOKUP function.

Calculating cost of goods sold

Cost of goods sold is the amount you paid for all the goods you sold. It is a critical component to calculating gross. If you use a perpetual inventory system, you calculate the cost of goods sold for every sale made. For simpler systems, however, you can calculate it based on a physical inventory at the end of the accounting period.

Figure 15.4 shows how to calculate the cost of goods sold with only the beginning and ending inventory counts and the total of all of the inventory purchased in the period.

Goods Available for Sale

• =SUM(C2:C3) 

Cost of Goods Sold

• =C4-C5 

The goods available for sale is the beginning inventory plus all of the purchases made. It is an intermediate calculation that shows what your ending inventory would be if you didn't sell anything.

The cost of goods sold calculation simply subtracts the ending inventory from the goods available for sale. If you had the goods at the start of the period or you bought them during the period but you don't have them at the end of the period, then they must have been sold.

Calculating return on assets

Return on assets (ROA) is a measure of how efficiently a business is using its assets to generate income. For example, a company with a higher ROA can generate the same profit as one with a lower ROA using fewer or cheaper assets.

To compute ROA, divide the profits for a period of time by the average of the beginning and ending total assets. Figure 15.5 shows a simple balance sheet and income statement and the resulting ROA.

=G15/AVERAGE(C12:D12) 

The numerator is simply the net profit from the income statement. The denominator uses the AVERAGE function to find the average total assets for the period.

Calculating return on equity

Another common profitability measure is return on equity (ROE). An investor may use ROE to determine whether their investment in the business is being put to good use. Like ROA, ROE divides net profit by the average of a balance sheet item over the same period. ROE, however, uses average total equity rather than average total assets. The formula to calculate ROE from Figure 15.5 is as follows:

=G15/AVERAGE(C25:D25) 

Calculating break even

A business may want to determine how much revenue it will need to achieve a net profit of exactly $0. This is called break even. The business will estimate its fixed expenses and estimate the percentage of each of its variable expenses. Using those numbers, it can back into a revenue amount that will result in the break even.

Figure 15.6 shows a break-even calculation. Column C shows either an F for a fixed expense or a percentage for an expense that varies as revenue changes. For example, research and development will be spent according to a budget and doesn't change if revenue increases or decreases. On the other hand, if the business pays a commission, the selling expenses will rise and fall with revenues.

The following formulas are used in Figure 15.6:

Operating Margin

• =SUM(D15:D18) 

Margin Net of Variable Expenses

• =SUM(D10:D13) 

Gross Margin

• =SUM(D7:D8) 

Revenue

• =ROUND(D8/(1-SUM(C4:C7)),0) 

The two variable expenses shown in Figure 15.6, cost of goods sold and selling expenses, are calculated by multiplying the revenue figure by the percentage. The formulas from Figure 15.6 are shown here:

Cost of Goods Sold

• =ROUND(D3*C4,0) 

Selling Expenses

• =ROUND(D3*C7,0) 

To build the break-even model in Figure 15.6, follow these steps:

1. Enter 0 into cell D18 to indicate zero net profit.
2. Enter the fixed expense amounts in column D next to their labels in column B.
3. Enter the percentage the company pays in commission in cell C7 (8% in this example).
4. Enter a percentage equal to 1 minus the expected gross margin in cell C4. In this example, the company expects a 60% gross margin percent, so 40% is entered in C4.
5. In cell D13, enter the formula for the operating margin shown earlier. The operating margin must be the sum of interest expense and other income and expense. As shown in Figure 15.6, if we estimate the interest expense to be $465 and the other income and expense to be $1,368, then the operating margin must be $1,833 for the net profit to be zero.
6. In cell D8, enter the formula for the margin net of variable expenses shown earlier. This calculation is operating margin plus the fixed operating expenses. It will drive the revenue calculation.
7. In cell D7, enter the formula for selling expenses shown earlier. We haven't entered the revenue formula yet, so this will be zero for now. But once revenue is entered, it will show the correct value.
8. Enter the formula for cost of goods sold in cell D4. Like the selling expenses formula, this will return zero until revenue is computed.
9. Finally, enter the formula for revenue in cell D3. The revenue calculation divides the margin net of variable expenses by 1 minus the sum of the variable percentages. In Figure 15.6, the two variable expenses will be 48% (40% plus 8%) of revenue. One minus that number, 52%, is divided into the margin net of variable expenses to get the revenue.

If this company makes a 60% gross margin, pays 8% in commissions, and has estimated the fixed expenses accurately, it will need to sell $16,935 to break even.

Calculating customer churn

Customer churn is the measure of how many customers you lose in a given period. It's an important metric in subscription-based businesses, although it's applicable to other revenue models as well. If your growth rate (the rate at which you are adding new customers) is higher than your churn rate, then your customer base is growing. If not, you're losing customers faster than you can add them, and something needs to change.

Figure 15.7 shows a churn calculation for a company with recurring monthly revenue. You need to know the number of customers at the beginning and end of the month and the number of new customers in that month.

Subscribers Lost

• =C2+C3-C4 

Churn Rate

• =C6/C2 

To determine the number of customers lost during the month, the number of new customers is added to the number of customers at the beginning of the month. Next, the number of customers at the end of the month is subtracted from that total. Finally, the number of customers lost during the month is divided by the number of customers at the beginning of the month to get the churn rate.

In this example, the business has a churn rate of 9.21%. It is adding more customers than it is losing, so that churn rate may not be seen as a problem. However, if the churn rate is higher than expected, the company may want to investigate why it's losing customers and change its pricing, product features, or some other aspect of its business.

Calculating annual churn rate

If a business has monthly recurring revenue, it means that customers sign up and pay for one month at a time. For those companies, it makes sense to calculate the churn rate on a monthly basis. Any new customers during the month will not churn in the same month because they've already paid for the month.

A typical magazine, however, signs up subscribers for an annual subscription. A meaningful churn rate calculation for them would be an annual churn rate. If a business wants to calculate a churn rate for a longer period than its recurring revenue model, such as calculating an annual churn for a business with monthly subscribers, the formula changes slightly. Figure 15.8 shows an annual churn rate calculation.

Annual churn rate: =C6/AVERAGE(C2,C4) 

The number of lost subscribers is divided by the average of beginning and ending subscribers. Because the period of the churn rate is different than the period of the recurring revenue, some of those 7,415 new subscribers canceled their subscriptions within the year, albeit in a later month than they first subscribed.

Calculating average customer lifetime value

Customer lifetime value (CLV) is a calculation that estimates the gross margin contributed by one customer over that customer's life. The churn rate calculated in Figure 15.7 is a component of CLV.

Figure 15.9 shows a calculation of CLV using the churn rate previously calculated. The first step is to calculate the average gross margin per customer.

Gross Margin

• =F2-F3 

Average Customer Margin

• =F4/AVERAGE(C4,C10) 

Customer Lifetime Value

• =F6/C7 

To calculate CLV, follow these steps:

1. Calculate the gross margin.
2. Calculate the average customer margin by dividing the gross margin by the average number of customers for the month. Because the gross margin was earned over the month, you have to divide by the average number of customers instead of either the beginning or ending customer count.
3. Calculate the CLV by dividing the average customer margin by the churn rate.

In this example, each customer will contribute an estimated $828.97 over their lifetime.

Calculating employee turnover

Employee turnover is a measure of how well an organization is hiring and retaining talent. A high turnover rate indicates that the organization is not hiring the right people or not retaining people, possibly because of inadequate benefits or below-average pay. Separations commonly include both voluntary and involuntary terminations.

Figure 15.10 shows the employment changes of an organization over a 12-month period. New hires are added to and separations are subtracted from the number of employees at the beginning of the month to get the ending employee count.

Average Monthly Employment

• =AVERAGE(F3:F14) 

Separations

• =SUM(E3:E14) 

Employee Turnover

• =F17/F16 

Employee turnover is simply the ratio of separations to average monthly employment. The AVERAGE function is used to calculate the average ending count of employees over the months. Separations are summed using SUM and are divided by the average monthly employments.

The result can be compared to industry averages or companies in the same industry. Different industries experience different turnover rates, so comparing them can lead to poor decisions. You don't have to calculate turnover for a 12-month period, but doing so removes seasonal employment variations that can skew results.

Converting interest rates

Two common methods for quoting interest rates are the nominal rate and the effective rate.

• Nominal Rate    This is the stated rate, and it is usually paired with a compounding period, for example, 3.75% APR compounded monthly. In this example, 3.75% is the nominal rate, APR is short for annual percentage rate (meaning that the rate is applied on an annual basis), and one month is the compounding period.
• Effective Rate    This is the actual rate paid. If the nominal rate period is the same as the compounding period, the nominal and effective rates are identical. However, as is usually the case, when the interest compounds over a shorter period than the nominal rate period, the effective rate will be higher than the nominal rate.

  Figure 15.11 shows 12 compounding periods in the middle of a 30-year loan. The original loan was for $165,000, has a nominal rate of 3.75% APR compounded monthly, and calls for 30 annual payments of $9,169.68 each.

In each period that the interest compounds but no payment is made, the balance goes up by the amount of interest. When the payment is made, a little of it goes to the last month's interest, and the rest of it reduces the principal.

Cell F17 sums all of the interest compounded over the year, and cell F18 divides it by the beginning balance to get the effective rate. Fortunately, we don't have to create a whole amortization schedule to convert interest rates. Excel provides the EFFECT and NOMINAL worksheet functions to do that job.

Effective Rate

• =EFFECT(F20,12) 

Nominal Rate

• =NOMINAL(F23,12) 

Both EFFECT and NOMINAL take two arguments: the rate to be converted and the npery argument. The rate to be converted is the effective rate for NOMINAL and the nominal rate for EFFECT. The npery argument is the number of compounding periods in the nominal rate period. In this example, the nominal rate is annual because the term APR was used. There are 12 months in a year, so there are 12 compounding periods in our nominal rate. If, for example, you had a loan with an APR that compounded daily, the npery argument would be 365.

Creating a loan payment calculator

Excel's PMT worksheet function is used to calculate your monthly payment on a loan. You can hard-code the values, such as the loan amount and interest rate, into the function's arguments, but by entering those values in cells and using the cells as the arguments, you can easily change the values to see how the payment changes.

Figure 15.12 shows a simple payment calculator. The user enters values in C2:C4, and the payment is calculated in C6 with the following formula:

=PMT(C3/12,C4*12,C2,0,0) 

The PMT function takes three required arguments and one optional argument:

• Rate (required)    The rate argument is the annual nominal interest rate divided by the number of compounding periods in a year. In this example, the interest compounds monthly, so the interest rate in C3 is divided by 12.
• nper (required)    The nper argument is the number of payments that will be made over the life of the loan. Since our user input asks for years and our payments are monthly, the number of years in C4 is multiplied by 12.
• pv (required)    The pv argument, or present value, is the amount being borrowed. Excel's loan functions, of which PMT is one, work on a cash flow basis. When you think about present value and payments as cash inflows and outflows, it's easier to understand when the value should be positive or negative. In this example, the bank is loaning us $215,000, which is a cash inflow and thus positive. The result of the PMT function is a negative because the payments will be cash outflows.

Creating an amortization schedule

With the payment amount calculated, we can create an amortization schedule that will show how much of each payment is principal and interest and what the loan balance will be after each payment. Figure 15.13 shows a portion of the amortization schedule.

The columns are of the amortization schedule are detailed here:

• Pmt No    The number of the payment being made. A 1 is entered into D11. The formula =D11+1 is entered into D12 and copied down to D370 (our amortization schedule can handle 360 payments).
• Pmt Amt    The amount of the PMT calculation rounded to the nearest penny. While Excel can calculate a lot of decimal places, we can write a check only for dollars and cents. This means there will be a small balance at the end of the loan. The formula =-ROUND($C$6,2) is entered in E11 and filled down through E370.
• Principal    The amount of each payment applied to the loan balance. The formula =E11-G11 is entered in F11 and filled down through F370.
• Interest    The amount of each payment that is interest. The balance after the prior payment is multiplied by the interest rate divided by 12. The total is rounded to two decimal places. The formula =ROUND(H10*$C$3/12,2) is entered in G11 and filled down through G370.

• Balance    The balance of the loan after the payment. The formula =C2 is entered in H10 representing the original amount of the loan. Starting in C3 and continuing down to C370, the formula =H10-F11 reduces the balance by the principal portion of the payment.

In the example shown in Figure 15.13, the number of years was entered as 15, compared to 30 in Figure 15.12. Reducing the length of the loan increases the amount of the payment.

The final step is to hide rows beyond the loan term. This is done with conditional formatting that changes the font color to white. A white font color against a white background effectively hides the data. The formula for the conditional formatting is shown here and in Figure 15.14.

=$D12>$C$4*12 

This formula compares the payment number in column D to the number of years in C4 times 12. When the payment number is larger, the formula returns TRUE, and the white font color formatting is applied. When the payment number is less than or equal to the total number of payments, no conditional formatting is applied.

See Chapter 5, “Formatting Worksheets,” for more information on conditional formatting.

Creating a variable-rate mortgage amortization schedule

In Figure 15.13, we created an amortization schedule for a loan with a fixed interest rate. There are also loans where the rate changes at times during the life of the loan. Often, these loans have an interest rate that's tied to a published index, such as the London Interbank Offered Rate (LIBOR), plus a fixed percentage. Those interest rates are usually stated as “LIBOR plus 3%,” for example.

Figure 15.15 shows an amortization schedule for a loan with a variable interest rate. A Rate column was added to the amortization schedule so that it will be obvious when the changes occur. A separate table is used to record when the rate changes.

The Rate column contains the following formula to select the proper rate from the rate table:

=VLOOKUP(D11,$K$11:$L$23,2,TRUE) 

The Interest column formula changes to use the rate in column G rather than the rate in C3:

=ROUND(I10*G11/12,2) 

The Rate column uses a VLOOKUP with a fourth argument of TRUE. The fourth argument of TRUE requires that the rate table be sorted in ascending order. Then VLOOKUP looks up the payment number in the rate table. It doesn't require an exact match, but it returns the row where the next payment number is larger than the lookup value. For instance, when the lookup value is 16, VLOOKUP returns the second row of the rate table because the payment number in the next row, 98, is larger than the lookup value.

The interest rate column formula is similar to the one used in the example shown in Figure 15.13, except that the absolute reference to $C$3 is replaced by a reference to column G (G11 for the formula in row 11).

Using dates instead of payment numbers

The two amortization schedules for this section and the previous one use the payment number to identify each payment. In reality, those payments will be due on the same day of the month. This allows the amortization schedule to be used for loans that start on any date. Figure 15.16 shows an amortization schedule using dates.

To modify the schedule to show the dates, follow these steps:

1. Enter the first payment date in cell D11.
2. Enter the following formula in D12 and fill down:

   =DATE(YEAR(D11),MONTH(D11)+1,DAY(D11)) 

3. Change the Pmt No column in the rate table column to the date the rate changed.
4. Change the formula in the conditional formatting to the following formula:

   =$D12>=DATE(YEAR($D$11),MONTH($D$11)+($C$4*12),DAY($D$11)) 

Calculating depreciation

Excel provides a number of depreciation-related worksheet functions including DB, DDB, SLN, and SYD. In this section, we'll look at calculating straight line (SLN) and variable-declining balance (VDB) depreciation.

Figure 15.17 shows a depreciation schedule for five assets using the straight-line method and a half-year convention.

Columns B:E contain the following user-entered data:

• Asset No.    A unique identifier for each asset. It's not necessary for the schedule, but it is handy for keeping track of assets.
• Cost    The amount paid to put the asset in service. This includes the price paid for the asset, any taxes associated with purchase, the cost to ship the asset to its place of service, and any costs to install the asset so that it's ready for use. This is also known as basis or cost basis.
• Year Acquired    The year the asset was put into service. This may be different than the year the payment was made to purchase the asset. It determines when depreciation starts.
• Useful Life    The number of years you estimate the asset will provide service.

The formula shown in F3:N7 follows:

=IF(OR(YEAR(F$2)<$D3,YEAR(F$2)>$D3+$E3),0,SLN($C3,0,$E3))*IF(OR(YEAR (F$2)=$D3+$E3,YEAR(F$2)=$D3),0.5,1) 

The main part of this formula is SLN($C3,0,$E3). The SLN worksheet function computes the straight-line depreciation for one period. It takes three arguments: cost, salvage, and life. For simplicity, the salvage value for this example is set to zero, meaning that the asset's cost will be fully depreciated at the end of its useful life.

The SLN function is pretty simple. But this is a depreciation schedule, so there's more work to do. The first IF function determines if the column is within the asset's useful life. If the year of the date in F2 is less than the year acquired, then the asset isn't in service yet, and the depreciation is zero. If F2 is greater than the year acquired plus the useful life, the asset is already fully depreciated, and the depreciation is zero. Both of these conditionals are wrapped in an OR function so that if either is true, the whole expression returns TRUE. If both are FALSE, however, the SLN function is returned.

The second part of the formula is also an IF and OR combination. These conditional statements determine whether the year in F2 is either the first year of depreciation or the last year. If either is true, the straight-line result is multiplied by 0.5 representing the half-year convention employed here.

All the cell references in this formula are anchored so that the formula can be copied down and to the right and the cell references change appropriately. References to row 2 are anchored on the row so that we're always evaluating the date in row 2. References to the columns C:E are anchored on the columns, so Cost, Year Acquired, and Useful Life stay the same as the formula is copied.

Calculating accelerated depreciation

The straight-line method depreciates an asset equally over all of the years of its useful life. Some organizations use an accelerated method, a method that depreciates at a higher rate at the beginning of an asset's life and a lower rate at the end. The theory is that an asset loses more value when it is first put in service than in its last year of operation.

Excel provides the DDB function (double-declining balance) for accelerated depreciation. DDB computes what the straight-line method would be for the remaining asset value and doubles it. The problem with DDB is that it doesn't depreciate the whole asset within the useful life. The depreciation amount gets smaller and smaller, but it runs out of useful life before it gets to zero.

The most common application of accelerated depreciation is to start with a declining balance method, and once the depreciation falls below the straight-line amount, the method is switched to straight line for the remaining life. Fortunately, Excel provides the VDB function that has that logic built in. Figure 15.18 shows a depreciation schedule using the following VDB-based formula:

=IF(OR(YEAR(F$2)<$D3,YEAR(F$2)>$D3+$E3),0,VDB($C3, 0,$E3*2,IF(YEAR(F$2)=$D3,0,IF(YEAR(F$2)=$D3+$E3,$E3*2-1, (YEAR(F$2)-$D3)*2-1)),IF(YEAR(F$2)=$D3,1,IF(YEAR(F$2)=$D3+$E3, $E3*2,(YEAR(F$2)-$D3)*2+1)))) 

You might have noticed that this formula is a little more complicated than the SLN formula from the previous example. Don't worry, we'll step through it piece by piece so that you understand it:

=IF(OR(YEAR(F$2)<$D3,YEAR(F$2)>$D3+$E3),0,VDB(...)))) 

The first part of the formula is identical to the SLN formula shown earlier. If the date in row 2 is not within the useful life, the depreciation is zero. If it is, the VDB function is evaluated:

VDB($C3,0,$E3*2,starting_period,ending_period) 

The first three arguments to VDB are the same as the SLN arguments: cost, salvage value, and life. SLN returns the same value for every period so that we don't have to tell SLN which period to calculate. But VDB returns a different amount depending on the period. The last two arguments of VDB tell it which period to compute. The life in E3 is doubled, which will be explained in the next section.

Starting_period

• IF(YEAR(F$2)=$D3,0,IF(YEAR(F$2)=$D3+$E3,$E3*2-1,(YEAR(F$2)-$D3)*2-1)) 

None of Excel's depreciation functions take into account the convention. That is, Excel calculates depreciation as if you bought all of your assets on the first day of the year. That's not very practical. In this section, we're assuming a half-year convention so that only half of the depreciation is taken in the first and last years. To accomplish this with VDB, we have to trick Excel into thinking that the asset has twice its useful life.

For an asset with a five-year useful life, the period for the first year goes from 0 to 1. For the second year, the periods span 1–3. The third year spans periods 3–5. That continues until the last year, which spans 9–10 (10 is double the five-year life). The starting period portion of the formula evaluates like this:

• If the year to compute is the acquisition year, make the starting period 0.
• If the year to compute is the last year, make the starting period the useful life times 2 and subtract 1.
• For all other years, subtract the acquisition year from the year to compute, multiply by 2, and subtract 1.

The ending period portion of the formula is similar to the starting period portion. For the first year, it ends at period 1. For the last year, it ends at the useful life times 2. For the middle years, it does the same calculation except that it adds one instead of subtracting.

By doubling the useful life, say from 7 periods to 14 periods for a 7-year asset, we can introduce the half-year convention into a declining balance function like VDB.

Calculating present value

The time value of money (TVM) is an important concept in accounting and finance. The idea is that a dollar today is worth less than the same dollar tomorrow. The difference in the two values is the income that you can create with that dollar. The income may be interest from a savings account or the return on an investment.

Excel provides several functions for dealing with TVM, such as the PV function for calculating the present value. In its simplest form, PV discounts a future value amount by a discount rate to arrive at the present value. If I promise to pay you $10,000 one year from now, how much would you take today instead of waiting? Figure 15.19 shows how you would calculate that amount:

=PV(C4,C3,0,-C2) 

The present value calculator in Figure 15.19 suggests that you would take $9,434 now instead of $10,000 a year from now. If you took the $9,434 and were able to earn 6% over the next year, you would have $10,000 at the end of the year.

The PV function accepts five arguments:

• Rate    Also known as the discount rate, the rate argument is the return you think you could make on your money over the discount period. It is the biggest factor in determining the present value, and it can also be the hardest to determine. If you're conservative, you might pick a lower rate—something you're sure you can achieve. If you were to use the money to pay off a loan with a fixed rate, the discount rate would be easy to determine.
• nper    The nper is the period of time to discount the future value. In this example, the nper is 1 year and is entered in cell C3. The rate and the period must be in the same units. That is, if you enter an annual rate, nper must be expressed as years. If you use a monthly rate, nper must be expressed as months.
• pmt    The pmt argument is the regular payments received over the discount period. When there is only one payment, as in this example, that amount is the future value and the payment amount is zero. The pmt argument must also the match the nper argument. If your nper is 10 and you enter pmt, PV assumes that you'll get that payment amount 10 times over the discount period. The next example shows a present value calculation with payments.
• FV    The future value amount is the amount you will receive at the end of the discount period. Excel's financial functions work on a cash flow basis. That means the future value and present value have opposite signs. For this example, the future value was made negative so that the formula result would return a positive number.
• type    The type argument can be 0 if the payments are received at the end of the period or 1 if the payments are received at the beginning of the period. This argument has no effect on this example because our payment amount is 0. The type argument can be omitted in which case it is assumed to be 0.

Calculating the present value of future payments

Another use of PV is to calculate the present value of a series of equal future payments. If, for example, you owed $5,000 of rent for an office over the next 10 years, you can use PV to calculate how much you would be willing to pay to get out of the lease. Figure 15.20 shows the present value calculation for that scenario.

=PV(C4,C3,-C2,0,1) 

If your landlord thought he could make 3% on the money, he might be willing to accept $43,930 instead of ten $5,000 payments over the next 10 years. The type argument is set to 1 in this example because rents are usually made at the beginning of the period.

When used on payments, the PV function is actually taking the present value of each payment individually and adding up all the results. Figure 15.20 shows the calculation broken out by payment. The first payment's present value is the same as the payment amount because it's due now. The Year 2 payment is due one year from now, and it is discounted to $4,854. The last payment, due nine years from now, is discounted to $3,832. All of the present value calculations are added up. Fortunately, PV does all the heavy lifting for you.

Calculating net present value

The PV function used in Figure 15.20 can calculate the present value of future cash flows if all the cash flows are the same. But sometimes that's not the case. The NPV (net present value) function is Excel's solution to calculating the present value of uneven future cash flows.

Suppose someone wanted you to invest $30,000 in a new business. In exchange for your investment, you would be entitled to an annual dividend over the next seven years. The estimated amounts of those dividends are shown in the schedule in Figure 15.21. Further suppose that you would like to earn an 8% return on your money.

To determine whether this investment is worth your while, you can use the NPV function shown here to calculate the net present value of that investment:

=NPV(C2,C5:C11) 

NPV discounts each cash flow separately based on the rate, just like PV value does. Unlike PV, however, NPV accepts a range of future cash flows rather than just a single payment amount. NPV doesn't have an nper argument because the number of values in the range determines the number of future cash flows.

Although the payments can be for different amounts, they are still assumed to be at regular intervals (one year in this example). And like the other TVM functions in this chapter, the rate period must be consistent with the payment period. In this example, the 8% return you'd like is an annual return, and the payments are annual, so they match. If you were getting a quarterly dividend, you would have to adjust the rate to a quarterly return.

The NPV for these cash flows calculates to $33,068. Since the required investment to get those cash flows, $30,000, is less than the NPV (and assuming the estimates are correct), these would be good investments. In fact, this data shows that you would make something more than the 8% return you wanted.

Calculating positive and negative cash flows

In the previous example, you were asked to make a large up-front investment to get future cash flows. Another scenario where NPV can be used is when you make smaller payments at the beginning of the investment period with the expectation of future cash inflows at the end.

Instead of one $30,000 payment, assume that you would only have to invest $15,000 the first year, $10,000 the second year, and $5,000 the third year. The amount you're required to invest goes down as the business grows and is able to use its own profits to grow. By year 4, no further investment is required, and it's expected that the business will be profitable enough to start paying a dividend.

Figure 15.22 shows a schedule where you pay in for the first three years and get money back the last four. The NPV function is the same as before; only the inputs have changed.

=NPV(C2,C5:C11) 

In the first NPV example, the amount invested was not part of the calculation. We simply took the result of the NPV function and compared it to the investment amount. In this example, a portion of the investment is also in the future, so the invested amounts are shown as negatives (cash outflows), and the eventual dividends are shown as positive amounts (cash inflows).

Instead of comparing the result to an initial investment amount, this NPV calculation is compared to zero. If the NPV is greater than zero, then the series of cash flows returns something greater than 8%. If it's less than zero, the return is less than 8%. Based on the data in Figure 15.22, it's a good investment.

Calculating an internal rate of return

In the previous example, we calculated the net present value of future expected cash flows and compared it to our initial investment amount. Because the net present value was greater than the initial investment, we knew that the rate of return would be greater than our desired rate. But what is the actual rate of return?

Excel's IRR function can be used to calculate the internal rate of return of future cash flows (see Figure 15.23). IRR is closely related to NPV. IRR computes the rate of return that causes the NPV of those same cash flows to be exactly zero.

For IRR, we have to structure our data a little differently. There has to be at least one positive and one negative cash flow in the values range. If you have all positive values, this means you invest nothing and receive only money. That would be a great investment but not very realistic. Typically, the cash outflows are at the beginning of the investment period, and the cash inflows are at the end. But it's not always that way, as long as there is at least one of each.

Note that we have to include the initial investment for IRR to work. The first row was added to show the initial $30,000 investment. The following IRR formula shows that the investment return is 10.53%:

=IRR(C3:C10,0.08) 

The first argument for IRR is the range of cash flows. The second argument is a guess of what is the internal rate of return. If you don't supply a guess, Excel uses 10% as the guess. IRR works by calculating the present value of each cash flow based on the guessed rate. If the sum of those is greater than zero, it reduces the rate and tries again. Excel keeps iterating through rates and summing present values until the sum is zero. Once the present values sum to zero, it returns that rate.

Calculating nonperiodic future cash flows

For both the NPV function and the IRR function, the future cash flows are assumed to be at regular intervals. That may not always be the case. For cash flows at irregular intervals, Excel provides the XIRR function.

XIRR requires one more argument than IRR: dates. IRR doesn't need to know the dates because it assumes that the cash flows are the same distance apart. Whether they are one day apart or one year, IRR doesn't care. The rate it returns will be consistent with the cash flows. That is, if the cash flows are annual, the rate will be an annual rate. If the cash flows are quarterly, the rate will be quarterly.

Figure 15.24 shows a schedule of nonperiodic cash flows. On some days, the investment loses money and requires a cash injection. On other days, the investment makes money and returns it to the investor. Over all the cash flows, the investor makes an annual return of 10.14%. The following formula uses XIRR to calculate the return:

=XIRR(C3:C17,B3:B17,0.08) 

Internally, XIRR works in much the same way as IRR. It calculates the present value of each cash flow individually, iterating through rate guesses until the sum of the present values is zero. It bases the present value calculations on the number of days between the current cash flow and the one just previous in date order. Then it annualizes the rate of return.

Performing financial forecasting

Forecasting refers to predicting values based on historical values. The values can be financial (for example, sales or income) or any other time-based data (for example, number of employees).

Excel 2019 makes the forecasting process easier than ever.

To create a forecast, start with historical, time-based data—for example, monthly sales. Figure 15.25 shows a simple example. Column B contains monthly sales data from 2012 through 2015. We also created a chart, which shows that sales tend to be cyclical, with lower sales during the summer months. The goal is to forecast the monthly sales for the next two years.

Start by selecting the data. For this example, we selected the range A1:B49. Choose Data ⇨ Forecast ⇨ Forecast Sheet, and Excel displays the Create Forecast Worksheet dialog box shown in Figure 15.26. (Options is clicked to display additional parameters.) The dialog box shows a chart that displays the historical data, the forecasted data, and the confidence limits for the forecast.

The confidence interval (depicted as thinner lines in the chart) determines the “plus or minus” values for the forecast and indicates the degree of confidence in the forecast. A higher confidence interval results in a wider prediction range. Note that the chart shown in the dialog box adjusts as you change options.

Click Create and Excel inserts a new worksheet that contains a table and a chart. Figure 15.27 shows part of this table. The table displays the forecasted values, along with the lower and upper confidence intervals. These values are generated using the new FORECAST.ETS and FORECAST.ETS.CONFINT functions. These are fairly complex functions, which explains why Excel does all the work.