Results 1 to 7 of 7

Reducing Balance Depreciation Formula

This is a discussion on Reducing Balance Depreciation Formula within the Excel Questions forums, part of the Question Forums category; Dear All, I am trying to find a formula to reconcile to schedule below Bitmap FIXED ASSETS From: 01/01/2007 Asset ...

  1. #1
    Biz
    Biz is offline
    Board Regular
    Join Date
    May 2009
    Location
    Perth, Australia
    Posts
    1,341

    Angry 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 endingOpening valueDepreciationDepreciationClosing valueBusinessClaim as tax
    31-Marratefor yearusededuction
    2,007 73,700 11.4% DV700 73,000 100%700
    2,008 73,000 11.4% DV8,322 64,678 100%8,322
    2,009 64,678 11.4% DV7,373 57,305 100%7,373
    2,010 57,305 11.4% DV6,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. #2
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,146

    Default 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.

    ξ

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #3
    Biz
    Biz is offline
    Board Regular
    Join Date
    May 2009
    Location
    Perth, Australia
    Posts
    1,341

    Default 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. #4
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,146

    Default 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.

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  5. #5
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,146

    Default 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".

    ξ

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  6. #6
    Biz
    Biz is offline
    Board Regular
    Join Date
    May 2009
    Location
    Perth, Australia
    Posts
    1,341

    Default Re: Reducing Balance Depreciation Formula

    Thanks probably need to play around as you suggested.

    Biz

  7. #7
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,146

    Default 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.

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com