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

Buy Date

F6 Price

G6 Year

H6 Salvage

I6 Life

J6 Method

K6 Buying Year/Days

2000

2001

2002

2003

2004

2005

2006

2007

2008

2009

2010

2011

2012

2013

2014

2015

2016

2017

2018

2019

2020

2021

2022

2023

2024

2025

2026

2027

2028

2029

<tbody>
</tbody>

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))))))," - ")

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

<tbody>
</tbody>

Sheet name Methods

	A	B	C
1	methods	factors	noswitch
2	SL	n/a	n/a
3	SYOD	n/a	n/a
4	DDB	2	TRUE
5	DDB-SL	2	FALSE
6	1.5DB	1.5	TRUE
7	1.5DB-SL	1.5	FALSE

<tbody>
</tbody>

Appreciate your help in this to reach the target.

 

[mba_110]

Can Anyone help me here ?