VDB function

[krp7168]

Hi All,

I understand that if you set the no_switch argument of the VDB function to false, your calculation will switch to straight line (SLN) once SLN is great than the declining balance.

However, I cannot seem to get this to work. By way of example:

=SLN(2400,300,10) equates to 210 of depreciation expense per year (for ten years)

=VDB(2400,300,10,0,1,2,FALSE) equates to the following depreciation expenses per year:

Year 1 480.00
Year 2 384.00
Year 3 307.20
Year 4 245.76
Year 5 196.61
Year 6 157.29
Year 7 125.83
Year 8 100.66
Year 9 80.53
Year 10 22.12

My question is why doesn’t this formula switch to straight-line in year 5 when the VDB balance is obviously less than straight-line????

Any guidance would be much appreciated!

Kevin

 

[sunnyland]

Hello again,

Sorry, I posted my previous post too quickly and only realised later it didn't paste all the rows. So here we are again.
The vdb function switches when the no switch off is less then the no switch on. See sample above. While in green both are similar with the blue it shows that switching to straight line is better greater amount of amortisation.

vdbcheck.xls
	A	B	C	D	E	F	G
1	Cost	Residual	Year	TotalAmort	SLNMonthly
2	750000	50000	10	700000	$70,000.00
3		NoswitchStartYear	noswitchAmort	NoSwithEndYear	SwitchStartYear	swith	SwithEndYear
4	0	$750,000.00	$150,000.00	$600,000.00	$750,000.00	$150,000.00	$600,000.00
5	1	$600,000.00	$120,000.00	$480,000.00	$600,000.00	$120,000.00	$480,000.00
6	2	$480,000.00	$96,000.00	$384,000.00	$480,000.00	$96,000.00	$384,000.00
7	3	$384,000.00	$76,800.00	$307,200.00	$384,000.00	$76,800.00	$307,200.00
8	4	$307,200.00	$61,440.00	$245,760.00	$307,200.00	$61,440.00	$245,760.00
9	5	$245,760.00	$49,152.00	$196,608.00	$245,760.00	$49,152.00	$196,608.00
10	6	$196,608.00	$39,321.60	$157,286.40	$196,608.00	$39,321.60	$157,286.40
11	7	$157,286.40	$31,457.28	$125,829.12	$157,286.40	$35,762.13	$121,524.27
12	8	$125,829.12	$25,165.82	$100,663.30	$121,524.27	$35,762.13	$85,762.13
13	9	$100,663.30	$20,132.66	$80,530.64	$85,762.13	$35,762.13	$50,000.00
14	10	$80,530.64	#REF!	#REF!	$50,000.00	#REF!	#REF!
15			#REF!			$700,000.00
16
17		DarkGreen	$592,713.60		DarkGreen	$592,713.60
18		DarkGreen+lightGreen	$624,170.88		DarkGreen+lightGreen	$628,475.73
Sheet2