# IF function to find depreciation

#### panie

##### New Member
<!-- / post 2145468 popup menu --> <!-- / close content container --> <!-- / post #2145468 --> <!-- start content table --> <!-- open content container --> <!-- / start content table --> <!-- controls below postbits --> i have to find the depreciation value for a particular project, in order to find the NPV.

The depreciation method to be used is 'sum of digits method'
Life of the project= 5
Cost of investment is = USD 100, 000

Depreciation for 5 years using IF function?

NOTE: FOR THOSE WHO DOSENT KNOW WHAT 'SUM OF DIGITS' method is.

Year 0 1 2 3 4 5
sum 5 4 3 2 1 0 = 15

thus, year 01 depreciation= (100,000/15)* 5
year 02 = (100,000/15)*4

Logic: Higher depreciation is allocated to the first few years.

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### nachiketdp

##### Board Regular
Re: IF function to find depriciation

i guess there is no need to use the if funtion, jus use the foll formula in cell B1(or 1st row of any column and drag it down)

=100000*(row()/15)

if the initial investment/useful life is a variable, u may suitably give cell reference instead of the constants that i have used.

hope this helps,
Regards,
Nachiket

#### DonkeyOte

##### MrExcel MVP
I confess I'm not quite sure I know what you're looking to do ... perhaps something like the attached - ie est. accum. depreciate as at given point in time in life of asset ?

Excel Workbook
AB
1Years5
2Cost100,000.00
3
4Dep.6,666.67
5Year2
6Accum.20,000.00
Sheet1

#### DonkeyOte

##### MrExcel MVP
edit: scrap the above - I missed the point re: front loading...

B6: =\$B\$2-(((\$B\$1-(\$B\$5))*(2*\$B\$4+(((\$B\$1-(\$B\$5))-1)*\$B\$4)))/2)

Last edited:

#### c_m_s_jr

##### Well-known Member
Without a helper for the depreciate amount:
Excel Workbook
AB
1Cost\$ 100,000.00
2Years to Depreciate5
3Years Passed1
4Accum\$ 33,333.33
Sheet

#### DonkeyOte

##### MrExcel MVP
If only I were good at math .... thanks for the clean up c_m_s_jr

#### c_m_s_jr

##### Well-known Member
Oh please Donkey. I've seen many of your post and you've cleaned up far more of my formulas. Besides I am an accountant by trade so you could argue I "cheated" since I deal with this kind of stuff all the time.

Now if I were only good at VBA.

#### panie

##### New Member
Re: IF function to find depriciation

thankss nachiket.. i knw the answer can be derived pretty easily with a simple formula. bt my teacher is complicating my life by asking to incorporate an if function to the sum. lol

BDW thanks for the tip!!

i guess there is no need to use the if funtion, jus use the foll formula in cell B1(or 1st row of any column and drag it down)

=100000*(row()/15)

if the initial investment/useful life is a variable, u may suitably give cell reference instead of the constants that i have used.

hope this helps,
Regards,
Nachiket

#### panie

##### New Member
woow! this is so great! this is my first visit to site and it is soo awasome!!!
and thankss for the great illustration too.
I wonder whether why does my teacher wants me to incorporate an if function here!!