Straight line depreciation with reverse ticker

In a project finance model, we often need to calculate depreciation of an asset which includes ongoing costs such as maintenance capital expenditure. This tutorial shows how to code the depreciation in one line as opposed to a traditional matrix method – hence reducing complexity and file size.

The straight line depreciation method is the most often used technique and can be formulated as:

Depreciation charge = Original cost of fixed asset / Useful life

For example, if construction capital expenditure costs $163.8 million and depreciates over 10 years, then the depreciation charge could simply be calculated as:

Depreciation per annum = $163.8 * 10% p.a. = $16.38 million or 
Depreciation per quarter = $16.38 million / 4 = $4.095 million

It is often in project financing that the project needs to maintain an ongoing capital expenditure which will be used to upgrade the existing capital assets or simply to maintain the scope of operations.

Screenshot 1. below shows typical capital expenditure inputs of a project finance transaction. In this example the project will incur $163.8 million construction capital expenditure and is required to maintain a capital expenditure of $0.5 million p.a. during operations. The question is how to calculate the depreciation charge of such cost?

Screenshot 1: Capital expenditure assumption

Traditional method of financial modelling of straight line depreciation

In this example, the model period is quarterly and hence we are calculating the depreciation on a quarterly basis. Hence, we are going to calculate the depreciation of the following costs over 10-years or 40 quarterly periods. 

• Construction capex of $163,800 
• Maintenance capex of approximately $125 thousand ($500 thousand per year / 4) incurred in every quarter

Screenshot 2 shows traditional way to code-in the depreciation. 

• Step 1: Transpose the period end in a column as shown in column C30 onwards.
• Step 2: Transpose the construction capex and the ongoing capex in column E30 onwards. In this example the construction capex is $163,800 at end of construction period (cell E41) and the project incurs ongoing capex of approximately $125 thousand per quarter (cell E42:E85). 
• Step 3: Bring-in depreciation rate in column F30 – it is 10% p.a. or 2.5% p.q. 
• Step 4: Depreciation is calculated for each costs incurred in the period. For example:

Construction capex (row 41) = $163,800 * 2.5%. = $4,095 thousand p.q. 
Ongoing capex (row 42:85) = $125 * 2.5% = $3 thousand p.q.

• Step 5: Refer to row 86 in the workbook. Add-up the depreciations for each cost and link this to row 23 for calculating the Net book value of the fixed asset.

Screenshot 2: Traditional method

The calculations are pretty straight forward and easy to understand. The drawback is the depreciation matrix involves many rows of calculations. Imagine if you need to repeat the same calculation for each type of asset class.

“Lite” method of financial modelling of straight line depreciation

This section will demonstrate an alternative way to code the depreciation in one line as opposed to the previous traditional matrix method. The formulas are more complicated but it only requires 3 steps of calculations. Once it is formulated correctly, it can be copied over to calculate depreciation of other types of assets and will be much neater compared to the traditional method.

Look at Screenshot 3 for this method. 

• Step 1: Refer to row 12. Code-in the reverse ticker or number of period(s) to look back for the capital expenditure base cost, which will be used in Sum (Offset) calculation in Step 2.

V12 (Mar-10) = MIN(COLUMN()-COLUMN($I12),1/$E17) = 13 periods; 
W12 (Jun-10) = MIN(COLUMN()-COLUMN($I12),1/$E17) = 14 periods; 
X12 (Sep-10) = MIN(COLUMN()-COLUMN($I12),1/$E17) = 15 periods;

and so on until maximum of 1 / 2.50% or 40 periods 

• Step 2: Refer to row 13. In this step, we code in the base cost for depreciation calculation.

V13 (Mar-10)=SUM(OFFSET(U16,0,0,1,-V12))=$163,800 
W13 (Jun-10)=SUM(OFFSET(V16,0,0,1,-W12))=163,800+123 = $163,923 
X13 (Sep-10)=SUM(OFFSET(W16,0,0,1,-X12))=163,800+123+125=164,048 

• Step 3: Code in the depreciation calculation as shown in Row 17.

V17 (Mar-10)= MIN(V13*$E17,V15) = MIN(2.5%*163800,163800) = $4,095
W17 (Jun-10)=MIN(W13*$E17,W15) = MIN(2.5%*163923,159828) = $4,098
X17 (Sep-10) =MIN(X13*$E17,X15) = MIN(2.5%*164048, 155855) = $4,101

Screenshot 3: Lite method

Comparison: Traditional vs. Lite method

Both methods should yield the same result, which is as reconciled in row 26.

Screenshot 4: Reconciliation Traditional and Lite method

Want more Tutorials?

See All Resources Get the latest Project Finance Tutorials and Blog Posts...