# 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

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
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!!

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...