IF function to find depreciation

panie

New Member
Joined
Dec 7, 2009
Messages
4
<!-- post 2145468 popup menu --> <table border="0" cellpadding="4" cellspacing="1"> <tbody><tr> <td class="thead">panie</td> </tr> <tr><td class="vbmenu_option">View Public Profile</td></tr> <tr><td class="vbmenu_option">Send a private message to panie</td></tr> <tr><td class="vbmenu_option">Find More Posts by panie</td></tr> <tr><td class="vbmenu_option">Add panie to Your Contacts</td></tr> </tbody></table>
<!-- / 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.
 

Some videos you may like

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
Joined
Jan 31, 2007
Messages
53
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
Joined
Sep 6, 2002
Messages
9,123
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
Joined
Sep 6, 2002
Messages
9,123
edit: scrap the above - I missed the point re: front loading...

Front Loaded Version would be:

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
Joined
Mar 23, 2009
Messages
1,561
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
Joined
Sep 6, 2002
Messages
9,123
If only I were good at math .... :rolleyes: thanks for the clean up c_m_s_jr
 

c_m_s_jr

Well-known Member
Joined
Mar 23, 2009
Messages
1,561
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
Joined
Dec 7, 2009
Messages
4
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
Joined
Dec 7, 2009
Messages
4
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!!
 

Watch MrExcel Video

Forum statistics

Threads
1,102,325
Messages
5,486,165
Members
407,536
Latest member
farrukhikram

This Week's Hot Topics

Top