IF condition for calculating depreciation

[mba_110]

Hi everyone,

i am struct in tricky part of If condition in excel spreadsheet, actually i am calculating the depreciation for my vehicles and partially succeeded but beginning year calculation is not showing correct results as i want the purchas year's cut of date should be on 31 December of same year, i have put the formula to calculate the days for first year, so it should be based on that days for 1st Year of calculation and the rest will be continue as normal calculation based on method i have selected in criteria of other cell.

I have methods sheet that calculate the depreciation based on selected method on result area, for more clarification i have mentioned the spreadsheet view for your reference.

The problem is in 1st year of calculation that need to be restrict to same year end and from next year it will be full, based on how many years i have selected in calculation for depreciation.

B6[TABLE="width: 2482"]
<tbody>[TR]
[TD="class: xl70, width: 71"]Buy Date[/TD]
[TD="class: xl72, width: 70"]F6 Price[/TD]
[TD="class: xl72, width: 45"]G6 Year[/TD]
[TD="class: xl72, width: 67"]H6 Salvage[/TD]
[TD="class: xl72, width: 39"]I6 Life[/TD]
[TD="class: xl72, width: 66"] J6 Method [/TD]
[TD="class: xl74, width: 70"]K6 Buying Year/Days[/TD]
[TD="class: xl73, width: 16"][/TD]
[TD="class: xl72, width: 49"]2000[/TD]
[TD="class: xl72, width: 49"]2001[/TD]
[TD="class: xl72, width: 49"]2002[/TD]
[TD="class: xl72, width: 49"]2003[/TD]
[TD="class: xl72, width: 49"]2004[/TD]
[TD="class: xl72, width: 49"]2005[/TD]
[TD="class: xl72, width: 49"]2006[/TD]
[TD="class: xl72, width: 49"]2007[/TD]
[TD="class: xl72, width: 49"]2008[/TD]
[TD="class: xl72, width: 49"]2009[/TD]
[TD="class: xl72, width: 49"]2010[/TD]
[TD="class: xl72, width: 49"]2011[/TD]
[TD="class: xl72, width: 49"]2012[/TD]
[TD="class: xl72, width: 73"]2013[/TD]
[TD="class: xl72, width: 73"]2014[/TD]
[TD="class: xl72, width: 66"]2015[/TD]
[TD="class: xl72, width: 66"]2016[/TD]
[TD="class: xl72, width: 73"]2017[/TD]
[TD="class: xl72, width: 66"]2018[/TD]
[TD="class: xl72, width: 66"]2019[/TD]
[TD="class: xl72, width: 66"]2020[/TD]
[TD="class: xl72, width: 66"]2021[/TD]
[TD="class: xl72, width: 66"]2022[/TD]
[TD="class: xl72, width: 66"]2023[/TD]
[TD="class: xl72, width: 66"]2024[/TD]
[TD="class: xl72, width: 66"]2025[/TD]
[TD="class: xl72, width: 66"]2026[/TD]
[TD="class: xl72, width: 66"]2027[/TD]
[TD="class: xl72, width: 66"]2028[/TD]
[TD="class: xl72, width: 66"]2029[/TD]
[/TR]
</tbody>[/TABLE]


Present formula is
=IF(AND(M$6>=$G7,M$6<=$I7+$G7-1),IF(ISERROR(MATCH($J7,methods,0)),"n/a",IF($J7="SL",SLN($F7,$H7,$I7),IF($J7="SYOD",SYD($F7,$H7,$I7,M$6-$G7+1),VDB($F7,$H7,$I7,M$6-$G7,M$6-$G7+1,INDEX(factors,MATCH($J7,methods,0)),INDEX(noswitch,MATCH($J7,methods,0))))))," - ")

So, i need to add the Buying Year/days to restrict it for current year of purchase and then leave it in serial for remaining years based on calculation method and salvage value i have selected.

 

[jorismoerings]

Hi,

You need to align the periods used in your depreciation formula. So as an example if the vehicle is purchased in June, you'll have 6 months left in the year.
using the VDB function your formula for the first year will look something like this: =VDB($F7,$H7,$I7*12;0;6) and for other year the 6 will change into 12.
Hope this helps.

 

[mba_110]

Thak you for your help if you could also mention exactly where i have to input this figures because in VDB function i inserted but its giving error.

 

[jorismoerings]

I mixed some things.
Try it like this: -VDB($F7,$H7,$I7*12,0,6)

 

[mba_110]

My formula is look like this after your amendment and now its less the next year number which is 2014 in cell AB7 and formula is showing wrong result.

=IF(AND(AB$6>=$I7,AB$6<=$K7+$I7-1),IF(ISERROR(MATCH($L7,methods,0)),"n/a",IF($L7="SL",SLN($H7,$J7,$K7),IF($L7="SYOD",SYD($H7,$J7,$K7,AB$6-$I7+1),-VDB($F7,$H7,$I7*12,0,6,INDEX(factors,MATCH($L7,methods,0)),INDEX(noswitch,MATCH($L7,methods,0))))))," - ")

[TABLE="width: 2892"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]H
[/TD]
[TD="align: center"]I
[/TD]
[TD="align: center"]J
[/TD]
[TD="align: center"]K
[/TD]
[TD="align: center"]L
[/TD]
[TD="align: center"]M
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]O
[/TD]
[TD="align: center"]P
[/TD]
[TD="align: center"]Q
[/TD]
[TD="align: center"]R
[/TD]
[TD="align: center"]S
[/TD]
[TD="align: center"]T
[/TD]
[TD="align: center"]U
[/TD]
[TD="align: center"]V
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]Buy Date[/TD]
[TD="align: center"]Price[/TD]
[TD="align: center"]Year[/TD]
[TD="align: center"]Salvage[/TD]
[TD="align: center"]Life[/TD]
[TD="align: center"]Method[/TD]
[TD="align: center"]Buying Year/Days[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2000[/TD]
[TD="align: center"]2001[/TD]
[TD="align: center"]2002[/TD]
[TD="align: center"]2003[/TD]
[TD="align: center"]2004[/TD]
[TD="align: center"]2005[/TD]
[TD="align: center"]2006[/TD]
[TD="align: center"]2007[/TD]
[TD="align: center"]2008[/TD]
[TD="align: center"]2009[/TD]
[TD="align: center"]2010[/TD]
[TD="align: center"]2011[/TD]
[TD="align: center"]2012[/TD]
[TD="align: center"]2013[/TD]
[TD="align: center"]2014[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]2016[/TD]
[TD="align: center"]2017[/TD]
[TD="align: center"]2018[/TD]
[TD="align: center"]2019[/TD]
[TD="align: center"]2020[/TD]
[TD="align: center"]2021[/TD]
[TD="align: center"]2022[/TD]
[TD="align: center"]2023[/TD]
[TD="align: center"]2024[/TD]
[TD="align: center"]2025[/TD]
[TD="align: center"]2026[/TD]
[TD="align: center"]2027[/TD]
[TD="align: center"]2028[/TD]
[TD="align: center"]2029[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]8-Dec-13[/TD]
[TD="align: right"]681,000[/TD]
[TD]2013[/TD]
[TD="align: right"]47,670[/TD]
[TD]10[/TD]
[TD]1.5DB-SL[/TD]
[TD]23[/TD]
[TD][/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD]678,986.00[/TD]
[TD]86,827.50[/TD]
[TD]73,803.38[/TD]
[TD]62,732.87[/TD]
[TD]53,322.94[/TD]
[TD]50,898.66[/TD]
[TD]50,898.66[/TD]
[TD]50,898.66[/TD]
[TD]50,898.66[/TD]
[TD]50,898.66[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[/TR]
</tbody>[/TABLE]


Sheet name Methods
[TABLE="width: 264"]
<tbody>[TR]
[TD="align: left"][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[/TR]
[TR]
[TD="align: left"]1[/TD]
[TD="align: left"]methods[/TD]
[TD="align: left"]factors[/TD]
[TD="align: left"]noswitch[/TD]
[/TR]
[TR]
[TD="align: left"]2[/TD]
[TD="align: left"]SL[/TD]
[TD]n/a[/TD]
[TD]n/a[/TD]
[/TR]
[TR]
[TD="align: left"]3[/TD]
[TD="align: left"]SYOD[/TD]
[TD]n/a[/TD]
[TD]n/a[/TD]
[/TR]
[TR]
[TD="align: left"]4[/TD]
[TD="align: left"]DDB[/TD]
[TD]2[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: left"]5[/TD]
[TD="align: left"]DDB-SL[/TD]
[TD]2[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: left"]6[/TD]
[TD="align: left"]1.5DB[/TD]
[TD]1.5[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: left"]7[/TD]
[TD="align: left"]1.5DB-SL[/TD]
[TD]1.5[/TD]
[TD]FALSE[/TD]
[/TR]
</tbody>[/TABLE]

Appreciate your help in this to reach the target.

 

[mba_110]

Can Anyone help me here ?