MBA's Guide to Microsoft Excel

Chapter 15

Understanding the Annuity or Sinking Fund Depreciation Starter Workbook

You can use the annuity or sinking fund depreciation starter workbook, shown in Figure 15-4, to construct depreciation schedules with the annuity or sinking fund depreciation methods. The annuity and sinking fund depreciation methods include, as part of the de- preciation expense, a return on the asset being depreciated. In general, this method violates the Generally Accepted Accounting Principles, and, for this reason, you are unlikely to need this starter workbook. However, public utilities sometimes use these methods for calculat- ing depreciation expenses—a practice that’s defensible because the rate-setting process often assumes a guaranteed return on investment. In general, you can use this template if man- agement feels that the rate setting virtually assures a specific return on assets and the an- nuity or sinking fund method has been selected and approved by management, appropriate regulatory agencies, and your external auditors.

Figure 15-4The annuity or sinking fund depreciation starter workbook.

Given four parameters—original cost, salvage value, the estimated useful life, and the speci- fied return on investment—this schedule calculates the period depreciation, the accumu- lated depreciation, the imputed (or assumed) investment revenue, and the net book value for each period of the forecasting horizon. You need this information to calculate business profits and losses, to report asset balances on the balance sheet, and to calculate any gains or losses on the disposal of assets.

The annuity or sinking fund depreciation starter workbook has two parts: the Annuity or Sinking Fund Depreciation Calculation Inputs box and the Annuity or Sinking Fund Depreciation Schedule.

Annuity or Sinking Fund Depreciation Calculation Inputs

The calculation inputs are Original Cost, Salvage Value, Estimated Life, and Specified Return. These are the only four variables you enter, and, unless you turn off cell protection, the four cells containing these values are the only cells within the spreadsheet in which you can enter data.

For convenience and good documentation within the starter workbook, cell B4 contains the original cost and is named Original_Cost, cell B5 contains the salvage value and is named Salvage_Value, cell B6 contains the estimated life and is named Estimated_Life, and cell B7 contains the specified return and is named Specified_Return. The formulas within the actual schedule use these cell names rather than the cell addresses.

Annuity or Sinking Fund Depreciation Schedule

The Annuity or Sinking Fund Depreciation Schedule has five columns: Period, Period Depreciation, Investment Revenue, Accumulated Depreciation, and Net Book Value.

Period

The period identifier simply numbers the time periods over which you’re depreciating the asset. The first period identifier is stored in cell B12 as the integer 1. Periods that follow are stored as the previous period plus 1.

Period Depreciation

Period depreciation is the depreciation expense for the current period. If you’re using the starter workbook for depreciable assets accounting, the Period Depreciation expense is the debit component of a depreciation journal entry and ultimately shows up in the profit and loss statement. If you’re using the starter workbook as part of a financial forecast, you can include the Period Depreciation expense with other expenses in the profit and loss forecast. Addition- ally, any income tax effect of this noncash expense ripples through the cash flow statement.

The basic Period Depreciation formula is:

=-(Original_Cost -(Salvage_Value/(1+Specified_Return)^Estimated_Life))/ PV(Specified_Return,Estimated_Life,1)

The minus sign at the beginning of this formula is necessary because the PV function re- turns a negative value when all of its arguments are positive. You may note how this corre- sponds to the original equation for Period Depreciation as explained earlier in this chapter:

(Original Cost?(Present Value of the Salvage Value))/(Present Value Factor of an Ordinary Annuity for n Periods at i%)

The Present Value of the Salvage Value variable takes the final Salvage Value and factors it based on the specified return for the estimated life of the asset.

The formula for the second period, however, is modified so that the asset is not depreci- ated below its salvage value. Starting with the second period, the formula to calculate the Period Depreciation expense is enclosed in an IF statement that first verifies that the asset hasn’t already been fully depreciated. For the comparison, rounded amounts are used so that trailing digits of insignificance don’t affect the test. The formula for the second period is:

IF(ROUND(Original_Cost-E12,0)=ROUND(Salvage_Value,0)0, -(Original_Cost-(Salvage_Value/(1+Specified_Return)^Estimated_Life))/ PV(Specified_Return,Estimated_Life,1))

In other words, this equation states that if the net book value after depreciation equaled the salvage value after the last period, set the depreciation for this period to 0.

Investment Revenue

Investment revenue is the assumed investment return on the asset. If you’re using the starter workbook for depreciable assets accounting and have selected the annuity depreciation method, the investment revenue for a period is credited to an investment revenue account and ultimately shows up in the profit and loss statement. If you’re using the starter work- book for depreciable assets accounting and have selected the sinking fund depreciation method, the investment revenue for a period is credited to the depreciation expense account. This results in a net debit to the depreciation expense account equal to the return of the asset principal and the increase in the accumulated depreciation expense for the period. The net depreciation expense ultimately shows up in the profit and loss statement. If you’re using the template as part of a financial forecast, you can add the investment revenue to other miscellaneous revenues in the profit and loss forecast.

The first-period Investment Revenue value is the Original Cost value times the Specified Return value. The formula for the first period is:

=Specified_Return*Original_Cost

The formula for the second period, however, is modified so that the previous Net Book Value amount is used and so that investment revenue isn’t calculated when the asset is fully de- preciated. Starting in the second period, the formula to calculate the Investment Revenue amount is enclosed in an IF statement that first verifies that the asset hasn’t already been fully depreciated. For the comparison, rounded amounts are used so that trailing digits of insignificance don’t affect the test. The formula for the second period is:

=IF(ROUND(Original_Cost-E12,0)=ROUND(Salvage_Value,0)0,Specified_Return*(Original-Cost-E12))

The Original_Cost–E12 portion is the Net Book Value amount at the end of the previous period. In subsequent periods, this part of the formula changes so that it always uses the Accumulated Depreciation amount from the previous period.

Accumulated Depreciation

If you’re using the starter workbook for depreciable assets accounting, the incremental in- crease in the Accumulated Depreciation amount is the credit component of a depreciation journal entry and ultimately shows up on the balance sheet as an adjustment to the asset’s carrying cost. If you’re using the starter workbook as part of a financial forecast, you can include the Accumulated Depreciation amount with the original cost of the asset in the balance sheet forecast to show the asset’s net book value. Alternatively, you might simply use the Net Book Value amount calculated in this schedule.

The first period’s Accumulated Depreciation balance is the cumulative return of principal calculated as the cumulative depreciation expense net of the cumulative assumed investment revenue. The formula for the first period is:

=SUM(C$12:C12)-SUM(D$12:D12)

The formula for the second period is:

=SUM(C$12:C13)-SUM(D$12:D13)

and so on.

Net Book Value

The net book value is an asset’s carrying cost and is the amount that you report either in- dividually or with other assets’ net book values on any historical or pro forma balance sheets.

For each period, the Net Book Value amount is the Original Cost amount less any accu- mulated depreciation. The formula for the first period is:

=Original_Cost-E12

The formula for the second period is:

=Original_Cost-E13

and so on.

Chapter Contents: