Excel 2010 Financials Cookbook

Powerful techniques for financial organization, analysis, and presentation in Microsoft Excel

(For more resources on this subject, see here.)

Calculating the depreciation of assets

Assets within a business are extremely important for a number of reasons. Assets can become investments for growth or investments in another line of business. Assets can also take on many forms such as computer equipment, vehicles, furniture, buildings, land, and so on. Assets are not only important within the business for which they are used, but they are also used as a method of reducing the tax burden on a business.

As a financial manager, you are tasked with calculating the depreciation expense for a laptop computer with a useful life of five years.

In this recipe, you will learn to calculate the depreciation of an asset over the life of the asset.

Getting ready

There are several different methods of depreciation. A business may use straight-line depreciation, declining depreciation, double-declining depreciation, or a variation of these methods. Excel has the functionality to calculate each of the methods with a slight variation to the function; however, in this recipe, we will use a straight-line depreciation. Straight-line depreciation provides equal reduction of an asset over its life.

Getting ready

We will first need to set up the Excel worksheet to hold the depreciation values for the laptop computer:

1. In cell A5 list Year and in cell B5 list 1.

   This will account for the depreciation for the year that the asset was purchased.

2. Continue this list until all five years are listed:

   

   

3. In cell B2, list the purchase price of the Laptop computer; the purchase price is $2500:

   

   

   In cell B4, we will need to enter the salvage value of the asset. The salvage value will be the estimated resale value of the asset when it is useful life, as determined by generally accepted accounting principles, has elapsed.

4. Enter $500 in cell B3:

   

5. In cell B5 enter the formula =SLN($B$2,$B$3,5) and press Enter:

   

6. Copy the formula from cell C5 and paste it through cell C9:

   

Excel now has listed the straight-line depreciation expense for each of the five years. As you can see in this schedule, the depreciation expense remains consistent through each year of the asset’s useful life.

How it works…

Straight-line depreciation calculates the value of the purchase price minus the salvage price, and divides the remainder across the useful life.

The function accepts inputs as follows =SLN(purchase price, salvage price, useful life).

There’s more…

Other depreciation methods are as follows:

Calculating the future versus current value of your money

When working within finance, accounting, or general business it is important to know how much money you have. However, knowing how much money you have now is only a portion of the whole financial picture. You must also know how much your money will be worth in the future. Knowing future value allows you to know truly how much your money is worth, and with this knowledge, you can decide what you need to do with it.

As a financial manager, you must provide feedback on whether to introduce a new product line. As with any new venture, there will be several related costs including start-up costs, operational costs, and more. Initially, you must spend $20,000 to account for most start-up costs and you will potentially, for the sake of the example, earn a profit of $5500 for five years. You also know due to expenditures, you expect your cost of capital to be 10%.

In this recipe, you will learn to use Excel functions to calculate the future value of the venture and whether this proves to be profitable.

How to do it…

We will first need to enter all known values and variables into the worksheet:

1. In cell B2, enter the initial cost of the business venture:

   

2. In cell B3, enter the discount rate, or the cost of capital of 10%:

   

3. In cells B4 through B8, enter the five years’ worth of expected net profit from the business venture:

   

In cell B10, we will calculate the net present value:

1. Enter the formula =NPV(B3,B4:B8) and press Enter

   

   We now see that accounting for future inflows, the net present value of the business venture is $20,849.33. Our last step is to account for the initial start-up costs and determine the overall profitability.

2. In cell B11, enter the formula =B10/B2 and press Enter:

   

As a financial manager, we now see that for every $1 invested in this venture, you will receive $1.04 in present value inflows.

How it works…

NPV or net present value is calculated in Excel using all of the inflow information that was entered across the estimated period. For the five years used in this recipe, the venture shows a profit of $5500 for each year. This number cannot be used directly, because there is a cost of making money. The cost in this instance pertains to taxes and other expenditures.

In the NPV formula, we did not include the initial cost of start-up because this cost is exempt from the cost of capital; however, it must be used at the end of the formula to account for the outflow compared to the inflows.

There’s more…

The $1.04 value calculated at the end of this recipe is also known as the profitability index. When this index is greater than one, the venture is said to be a positive investment.