Reducing Balance Depreciation Formula

Thanks:  0
Likes:  0

# Thread: Reducing Balance Depreciation Formula

1. ## Reducing Balance Depreciation Formula

Dear All,

I am trying to find a formula to reconcile to schedule below

Bitmap
 FIXED ASSETS
From: 01/01/2007AssetPurchaseDisposalDepreciationMnthsDispCumulative deprecciationNet BookMonth'sGain/
To: 31/03/10Catvaluedatedatevaluemethrateageageon dispOpeningCum.Valuedepr.(loss)
BuildingO73,700 01/03/0701/04/100 D11.4%46 37 22,956 0 22,956 0 0 (50,744)

Cell K13 uses C13*(1-(1-H13)^(L18/12))
which is Purchase value * (1-(1-rate)^Months/12))

I derive 22956 where as table derives 22928.

 Building Purchase date: 01 March 2007 Depreciation for the first year has been apportioned for the 1 months that you owned the asset. Year ending Opening value Depreciation Depreciation Closing value Business Claim as tax 31-Mar rate for year use deduction 2,007 73,700 11.4% DV 700 73,000 100% 700 2,008 73,000 11.4% DV 8,322 64,678 100% 8,322 2,009 64,678 11.4% DV 7,373 57,305 100% 7,373 2,010 57,305 11.4% DV 6,533 50,772 100% 6,533

Does anyone have formula for reducing balance which can match 22,928 which is total Deprecaition?

Kind Regards,

Biz

2. ## Re: Reducing Balance Depreciation Formula

Something exceptional happened in the first year. The \$700 was deducted as an allocation, and doesn't fit in with the rest of the schedule.

This works, though its something of a hack:
=(C13-700)*(1-(1-H13)^(L18/12))+700

You might need to adjust the number of months down one too (you are really depreciating from the beginning of the second year, plus the \$700 taken in the first year). Simplest would be to refer to the depreciation schedule itself as a firm number. Or add a column to show first year deprecation taken, and add that to the remaining depreciation taken starting with year two.

ξ

3. ## Re: Reducing Balance Depreciation Formula

Hi,

I really can't hardcode 700 in formula as it it not flexible.
Is there another way?

Biz

4. ## Re: Reducing Balance Depreciation Formula

Not that I can see. The first year is not the same as all the rest - your formula applies to the rest but not the first year.

5. ## Re: Reducing Balance Depreciation Formula

What I mean is that even if you used a formula it would have to be a two part formula, one for the first year and one for all the other years. Unless all your assets use the same special depreciation method for the first year, it will not be a general solution anyway. If you need it all on a spreadsheet, it might be best to create a column for first year depreciation, depreciation, and last year depreciation, with total depreciation the sum of the three. There are often funny things happening in year one and year n - no depreciation worksheet covers all possible cases (that I've seen). So you need to allow for some "wiggle room".

ξ

6. ## Re: Reducing Balance Depreciation Formula

Thanks probably need to play around as you suggested.

Biz

7. ## Re: Reducing Balance Depreciation Formula

Another strategy is to us a "beginning balance/net change/ending balance" approach. Your schedule would need to be reconciled at year end, tied out to the financial statements or tax returns. Then when you start the next year you bring forward the cost basis and accumulated depreciation as your starting point for the year.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•