Preparing Fixed Asset (CapEx) forecast model in Excel – Depreciation & NBV Calculations

In any financial model (forecasts or variance analysis) the idea is to derive expectations where business will end up if particular set of assumptions (scenario) prevails. These are basically calculations that help the decision making process.

Excel provides the necessary flexibility in designing such models. As calculations are based on assumptions, it is much easier for us to understand the effect of change in assumption and how the business and its resources will react.

Today we are looking at a simple example to understand how Excel can help us make Capital Expenditure forecasts and calculating net book value (NBV) each year using Excel formulas.

Scenario

We are required to make calculations with following information provided:

The forecast capital expenditures are as follows:

YearsCapex
201581
201655
201795
201813
201967
202018
202154
202258
202322
202486
202558
202697
202783
202821
202959
203071

Inflation rate of 5% per annum is expected. The useful life of asset is 5 years and thus fixed assets are depreciated on straight line basis over 5 years.

Clearing the approach

We need to do the following:

  1. Adjust the capital expenditure amounts for inflation
  2. Make up a formula to calculate depreciation of assets
  3. Calculate net book value of assets each year

A pretty simple thing to do right? But we have one problem here. As we are using straight line basis of depreciation, one might think of adding all the assets up and then dividing it over the useful life cycle to get the depreciation for each year.

This is true but only for the first five years. Once we enter the sixth year, the asset purchased in the first year will be disposed off and no longer with us, therefore shouldn’t be added in the total cost of the asset. To get around this problem we can:

  1. either use helper columns/rows
  2. or make up a formula with clever combination of functions

Making the forecast calculations

Step 1: Download the file and open it. You will see preliminary information already available arranged in row format. It looks like this:

fixed asset base
Click to expand

Step 2: First we need inflation multiplier so that we can calculate adjusted CAPEX for each year. To calculate inflation multiplier the formula is:

(1+r)^n

where r is rate of interest and n is the number of years. So the base year will be n = 0 and with every next year “n” will increase by 1. To do the calculations in Excel we can use the formula.

Go to cell C5 and put this formula:

=(1+$B$5)^(COLUMN()-3)

Drag the fill handle to cell R5.

B4 contains the percentage value and COLUMN() function help increase the value as we drag the fill handle. “-3” is appended at the end as we are starting from column 3 therefore to make the resultant equal to 0, a weight is added.

In cell C6 put this formula and drag the fill handle to R5: =C4*C5

Step 3: We assume the opening balance of fixed asset is zero, so enter 0 in cell C9. Row 10 is about additions (acquisition of fixed assets). It will be adjusted capital expenditure figures. Therefore, in cell C10 put the formula: =C6 and drag the fill handle to R9.

Step 4: If we have the depreciation figures, we can calculate the closing balance by adding opening balance and additions during the year and deducting the depreciation of the year amount. To calculate the closing balance put the following formula in cell C12 and drag the fill handle to cell R12:

=C9+C10-C11

Step 4: To calculate the depreciation go to cell C10 and put the following formula:

=(SUMPRODUCT($C$6:$R$6,--($C$7:$R$7<=(COLUMN()-2)))-IF(C7>$B$7,SUMPRODUCT($C$6:$R$6,--($C$7:$R$7<=(COLUMN()-($B$7+2))))))/$B$7

This formula might look daunting and before we dissect it, lets understand the situation the easy way. Remember we are using straight line basis for depreciation and useful life is 5 years for each asset. Therefore, every 5 years the oldest asset will complete useful life and will be disposed off, if we make the schedule for each asset over the years with total of assets and depreciation each year, it will look like this:

 Year AssetCostDep = Cost/5
201581810000000000000008116.2
201657.758157.7500000000000000138.7527.75
2017104.73758157.75104.73750000000000000243.487548.6975
201815.049138157.75104.737515.04913000000000000258.536651.707325
201981.438928157.75104.737515.0491381.4389200000000000339.975567.99510875
202022.97307057.75104.737515.0491381.4389222.973070000000000281.948656.38972238
202172.3651600104.737515.0491381.4389222.9730772.36516000000000296.563859.31275529
202281.6118200015.0491381.4389222.9730772.3651681.6118200000000273.438154.6876202
202332.50402000081.4389222.9730772.3651681.6118232.504020000000290.89358.17859915
2024133.41420000022.9730772.3651681.6118232.50402133.4142000000342.868368.57366071
202594.4758900000072.3651681.6118232.50402133.414294.4758900000414.371182.87422476
2026165.9029000000081.6118232.50402133.414294.47589165.90290000507.9089101.5817754
2027149.05610000000032.50402133.414294.47589165.9029149.0561000575.3531115.0706255
202839.59863000000000133.414294.47589165.9029149.056139.5986300582.4477116.4895479
2029116.816000000000094.47589165.9029149.056139.59863116.8160565.8495113.1698955
2030147.603900000000000165.9029149.056139.59863116.816147.6039618.9775123.795498

Use scroll bar to go through the whole data. To the right you can see the sum for each year and the depreciation calculation as well. This is exactly we have done in the formula above but it has saved us all of this work! Amazing isn’t it 🙂

Though I recommend everyone to go through an easier tutorial on SUMPRODUCT to understand how it works and also the use of “double dash” by reading: Conditional SUM in Excel with SUMPRODUCT function.

Note
There are other possible ways to do get the depreciation calculation done. For example using OFFSET function to cater the problem of useful life of asset. I will be covering the use of this function to do conditional sum in my future articles.

So once we have the depreciation calculation in place we have the forecast complete. And as formulas are based on cell values (for inflation, useful years) you can easily change them and model will update instantly.

The completed file looks like this:

fixed asset complete
Click to expand

You can download the fully worked file to play and learn. Enjoy!

Check out more articles to learn awesome use of conditional sum functions

  1. Create Edgy To-Do or Check Lists with Check boxes in Excel + Shortfall calculator
  2. Birth Dates Heat Map in Excel – How To
  3. Conditional SUM in Excel with SUMPRODUCT function

4 COMMENTS

  1. The formula for the inflation rate above says (1+$B$4) should be $B$5.

    • Thanks for pointing out. Its corrected

  2. The depreciation forecast working is excellent.

    • Thanks Saleem and welcome to PakAccountants!

LEAVE A REPLY

Please enter your comment!
Please enter your name here