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.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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!!
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top