Question

A B с D E F A B C с E Amortization Table Double Declining Depreciation Table 1 2 3 4 Loan 5 Rate 6 Term(Year) Payment 7. Period 1 9 2 1 2 3 4 5 6 Value Life $2,000.00 5 7 Year 8 9 3 10 Value at Depreciation Total Depreciation Begin of Year During Year To End of Year 1 $2,000.00 $800.00 $800.00 2 $1,200.00 $480,00 $1,280.00 3 $720.00 $288.00 $1,568.00 4 4 $432.00 $172.80 $1,740.80 5 $259.20 $259,20 $2,000.00 4 $5,000.00 5% 1 Monthly Amount to Amount to Remaining Total interest Payment Interest Principal Balance Payment $428.04 $20.83 $407.20 $4,592.80 $20.83 $428.04 $19.14 $ $408.90 $4,183.90 $39.97 $428.04 $17.43 $410.60 $3,773.29 $57.40 $428.04 $15.72 $412.32 $3,360.98 $73.12 TOY $428.04 $14.00 $414.03 $2,946.94 $87.13 $428.04 $12.28 $415.76 $2,531.18 $99.41 $428.04 $10.55 $417.49 $2,113.69 $109.95 $428.04 $8.81 $419.23 $1,694.46 $118.76 $428.04 $7.06 $420.98 $1,273.49 $125.82 $428.04 $5.31 $422.73 $850.75 $131.13 $428.04 $3.54 $424.49 $426.26 $134.67 $428.04 $1.78 $426.26 $0.00 $136.45 5 11 12 6 7 10 11 12 13 14 15 16 17 18 19 20 13 14 8 9 10 11 12 Part 3: Double Declining Depreciation Table For tax purpose an item may be depreciated over a period of several years N. With the double-declining balance method of depreciation, each year the item depreciates by: value at the beginning of that year * 2/N. For a $2000 value item of 5 years life, the first year's depreciation is calculated as: 2000*2/5. In the last year it is depreciated by its value at the beginning of the last year. Create a double-declining depreciation table as shown below and test your spreadsheet with property value = 2000 and life = 5. Use an IF function to compute each year's depreciation.

I Need help creating a formula, I'm not familiar with the if function, could somebody explain to me how to use it and find the correct formula for this problem?

Answer 1

If function is used in excel when we have to make calculations based on some condition.

Its syntax is :

IF(logical_test, [value_if_true], [value_if_false])

Logical test is the condition we want to fulfill.

Value if true means the value we want to get, if the condition is fulfilled.

Value if false means the value we want to get , if condition is not fulfilled.

In the above example we want to calculate depreciation on basis of double declining balance method.

For every year , except last year, we want depreciation to be Opening Balance*Current Year/5 ; if the last year is there we want depreciation to be equal to opening balance.

Therefore, following formula can be applied to calculate the depreciation.

=IF(YEAR<>5,Opening balance*2/5,Opening Balance)

(<> is used to show not equal to)

The formula shows if year is not 5, then depreciation is equal to Opening balance*2/5, otherwise(if year is 5) it will be equal to the opening balance of last year.

Year(A4)	Opening Balance	Depreciation	Closing Balance	Total Depreciation at the end of year
1	2000	=IF(A5<>5,B5*2/5,B5)	=B5-C5	=SUM(C5)
2	=D5	=IF(A6<>5,B6*2/5,B6)	=B6-C6	=SUM($C$5:C6)
3	=D6	=IF(A7<>5,B7*2/5,B7)	=B7-C7	=SUM($C$5:C7)
4	=D7	=IF(A8<>5,B8*2/5,B8)	=B8-C8	=SUM($C$5:C8)
5	=D8	=IF(A9<>5,B9*2/5,B9)	=B9-C9	=SUM($C$5:C9)

Year(A4)	Opening Balance	Depreciation	Closing Balance	Total Depreciation at the end of year
1	2000	800	1200	800
2	1200	480	720	1280
3	720	288	432	1568
4	432	172.8	259.2	1740.8
5	259.2	259.2	0	2000