Sum function from VLOOKUP results

[r_worley]

I'd like to have my VLOOKUP function work with a sum function, but do not know how to formulate. Example: Calculating Previous Depreciation

YEAR OF LIFE_DEPREC METHOD__USEFUL LIFE__COST__PREV DEPREC_
3_____________MACR 5___________5________ 4,060_____ 1,949

The lower table represents rates of depreciation each year whic corresponds to the year of life an asset is in.

MACR 5
rate
1 .20000
2 .32000
3 .19200
4 .11520
5 .11520
6 .05760

I'd like Excel to use a VLOOKUP function to find the rates according to 'Year of Life' and then add each year's function of (cost * rate) up to the appropriate year. The formula must 'Year of Life' minus 1 to get 'Previous Deprec' Thanks for your help.

 

[Andrew Fergus]

Hi and welcome to MrExcel!

Before anyone answers this question, I think we need to know two additional things:
1) How did you arrive at the value 1,949?
2) I presume there are depreciation methods other than 'MACR5'?

Andrew

 

[r_worley]

The number is incorrect, I apologize. Instead of $1,949, it s/b $2,111. I've inherited the worksheet and am still checking everything. That amount is from multiplying cost by the applicable table rates for each year I need depreciation for and summing the results. Year one depreciation is $812 (4,060*.20000), Year two is $1,299 (4,060*.32000), etc. Thanks.

 

[r_worley]

There are additional depreciation methods. I primarily use tables for 3-year, 5-year, 7-year, 15-year and 20-year, but all have tables like the one in the example.

 

[Andrew Fergus]

Hi

Start by setting up a table of depreciation rates like this:

   K                    L       M      N      O      
 1 Depreciation Methods                              
 2 Year                 MACR1   MACR2  MACR5  MACR9  
 3 0                    0.00%   0.00%  0.00%  0.00%  
 4 1                    100.00% 67.00% 20.00% 10.00% 
 5 2                            33.00% 32.00% 10.00% 
 6 3                                   19.20% 10.00% 
 7 4                                   11.52% 10.00% 
 8 5                                   11.52% 10.00% 
 9 6                                   5.76%  10.00% 
10 7                                          10.00% 
11 8                                          10.00% 
12 9                                          20.00% 
13 10                                                
Sheet1
[Table-It] version 09 by Erik Van Geit

I used the range K1:O13 on sheet1. If you use a different range then you will need to change the following formula (see my next post). Modify the headings and rates and the size of the table to suit the methods you actually use.

Andrew

 

[Andrew Fergus]

Then I used the following layout and test data. Note the array formula for the 'Prev Depn' column - there are instructions on how to use that below.

  A     B            C      D    E     F         G         
1 Asset Year of Life Method Life Cost  Prev Depn Curr Depn 
2 1     3            MACR5  5    4060  2111      780       
3 2     1            MACR1  1    2000  0         2000      
4 3     2            MACR1  1    1000  1000      0         
5 4     8            MACR9  9    10000 7000      1000      
Sheet1
[Table-It] version 09 by Erik Van Geit

RANGE FORMULA (1st cell)
F2:F5 {=ROUND(E2*SUM(IF(C2=$L$2:$O$2,IF(B2>$K$3:$K$13,$L$3:$O$13,0),0)),0)}
G2:G5 =ROUND(E2*INDEX($L$3:$O$13,B2+1,MATCH(C2,$L$2:$O$2,0)),0)
{=formula}:
 select first cell
 enter formula without {}
 confirm with Control-Shift-Enter
 then copy down
[Table-It] version 09 by Erik Van Geit

Cheers
Andrew