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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

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,867
Messages
5,489,353
Members
407,686
Latest member
Chuck1960

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top