Need help building DDB from scratch

[Summers15]

I have to build a double declining depreciation from scratch without using the built in functions. It has to be for 5 years. Cost in B4 is 60,000. EUL in B5 is 5 years, and salvage in B6 is 20,000. Cell reference must be used at all times. I can get year 1 and 2 but I can not get 3-5 to come out. I am not even sure I am writing the formula right. I am new at building from scratch so putting it all together is complicated especially when I really have nothing to go off of for a tutorial.

[FDibbins]

Hi, welcome to the forum

It might help if we could see what you have so far?

[Summers15]

=($B$4/$B$5/$B$4*2)*$B$4
=($B$4/$B$5/$B$4*2)*($B$4-$C$21)
=($B$4/$B$5/$B$4*2)*($B$4-$C$22-$C$21)
=($B$4/$B$5/$B$4*2)*($B$4-C21-C22-C23)
=($B$4/$B$5/$B$4*2)*($B$4-C21-C22-C23-C24)

Double Declining Balance
yr 1 $20,000
yr 2 $12,000
yr 3 $7,200
yr 4 $4,320 (should be $800)
yr 5 $2,592 (should be 0)

Like I said, I am not even sure these first 3 are the correct formulas. Right answers but may not be the proper way to do ddb.

[FDibbins]

Well this...
=($B$4/$B$5/$B$4*2)*($B$4-$C$21)
=($B$4/$B$5/$B$4*2)*($B$4-$C$22-$C$21)
=($B$4/$B$5/$B$4*2)*($B$4-C21-C22-C23)
=($B$4/$B$5/$B$4*2)*($B$4-C21-C22-C23-C24)

can be simplified to this (I think, you will need to test)...
=($B$4/$B$5/$B$4*2)*($B$4-sum($C$21:C21))

[Summers15]

It doesn't work. It is still calculating yr 4 and year 5 the same. Some how have to make it say depreciate but do not go below the salvage in the 5 years. I apologize. The Cost is $50,000 and the Salvage is $10,000. It is still 5 years. Year 4 should calculate to $800 and year 5 $0. Because year 3 is 7200.00 the amount left of the $10,000 is $800. I can't figure how to write this formula. It is driving me crazy. I am sure it is something simple.

[FDibbins]

I didnt say that would solve your problem, I just said it woumd simplify what you already had

[MrShorty]

In addition to FDibbins simplification, B4/B4=1, so the part inside of the first parentheses can simplify to 2/B5. Simplifies the formula, but, if it is indeed wrong, it will still be wrong.

The help file for the DDB() function mentions a MIN() function: https://support.office.com/en-us/art...rs=en-US&ad=US Other Double declining Balance tutorials (not Excel specific) mention that the DDB is a correction to the linear depreciation. Perhaps you need to calculate both the DDB and the linear depreciation so you will know when to switch over to the linear depreciation towards the end. http://www.calculatorsoup.com/calcul...ing-double.php

[Summers15]

Oh sorry. The other thing to is that I can not use built in functions. So it all has to be from scratch.

[MrShorty]

That may be true, but is the basic mathematical idea correct? Is that how you would calculate DDB with pencil and paper, or am I misunderstanding how it is calculated?

If, when you say that cannot use any of Excel's built in functions (including SUM, IF, POWER, PRODUCT, etc.), then I think it will be essential for you to understand (and communicate to us, since you cannot assume that we know financial calculations such as this) the mathematical formulas that go into calculating DDB.

[Summers15]

Maybe I am in the wrong forum then. I thought I was asking for help in excel. The built in is =DDB(cost, salvage, life, period, [rate])

So it has to be written along the lines of the built in formula.

[FDibbins]

Why cant you use the built-in formula?

MrShorty, good catch with the calc, I missed that

[Summers15]

its the assignment. very frustrating. no tutorial just a "here you go, figure it out."

[FDibbins]

OK, so that is excel's built-in function, but what is the actual formula?

[shg]

Help for the DDB function explains exactly how it's calculated.