# Thread: IF function to find depreciation Thanks: 0 Likes: 0

1. ## IF function to find depreciation

 panie View Public Profile Send a private message to panie Find More Posts by panie Add panie to Your Contacts

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.  Reply With Quote

2. ## 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  Reply With Quote

3. ## Re: IF function to find depreciation

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 ?

Sheet1

 A B 1 Years 5 2 Cost 100,000.00 3 4 Dep. 6,666.67 5 Year 2 6 Accum. 20,000.00

 Cell Formula B4 =(B2/((B1*(2+(B1-1)))/2)) B6 =(\$B\$5*(2*\$B\$4+((\$B\$5-1)*\$B\$4)))/2

Excel tables to the web >> Excel Jeanie HTML 4  Reply With Quote

4. ## Re: IF function to find depreciation

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)  Reply With Quote

5. ## Re: IF function to find depreciation

Without a helper for the depreciate amount:

 A B 1 Cost \$ 100,000.00 2 Years to Depreciate 5 3 Years Passed 1 4 Accum \$ 33,333.33

 Cell Formula B4 =B1-((B2-B3)*((B2-B3)+1))/2*(B1/((B2*(B2+1))/2))

Excel tables to the web >> Excel Jeanie HTML 4  Reply With Quote

6. ## Re: IF function to find depreciation

If only I were good at math .... thanks for the clean up c_m_s_jr  Reply With Quote

7. ## Re: IF function to find depreciation

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.  Reply With Quote

8. ## 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!! Originally Posted by nachiketdp 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  Reply With Quote

9. ## Re: IF function to find depreciation

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!!  Reply With Quote

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•