[OASIS Issue Tracker] Updated: (OFFICE-2222) DDB: add formula

[OASIS Issues Tracker <workgroup_mailer@lists.oasis-open.org>]

     [ http://tools.oasis-open.org/issues/browse/OFFICE-2222?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Eike Rathke updated OFFICE-2222:
--------------------------------

    Proposal: 
Change Syntax "Integer period" to "Number period".

Add Constraints:
cost >= 0, salvage >= 0, salvage <= cost, 1 <= period <= lifeTime, declinationFactor > 0

The formula using integer periods according to http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_DDB_function would be

To calculate depreciation, DDB uses a fixed rate. When declinationFactor = 2 this is the double-declining-balance method (because it is double the straight-line rate that would depreciate the asset to zero). The rate is given by:

    rate = declinationFactor / lifeTime

The depreciation each year is calculated as

    depreciation_of_year = MINIMUM( book_value_at_start_of_year * rate; book_value_at_start_of_year - salvagevalue )

Thus the asset depreciates at rate until the book value is salvagevalue.

book_value_at_start_of_year_i = originalcost - sigma_i(1,year_i-1,depreciation_of_year_i)


The current formula is missing the MINIMUM evaluation and uses only (book_value_at_start_of_year * rate), hence the TODO in the draft.

Alternative algorithm allowing for non-integer periods:

rate = declinationFactor / lifeTime
if rate >= 1 then
    rate = 1
    if period == 1 then
        oldValue = cost
    else
        oldValue = 0
    endif
else
    oldValue = cost * pow( 1 - rate, period - 1 )
endif
newValue = cost * pow( 1 - rate, period )
if newValue < salvage then
    DDB = oldValue - salvage
else
    DDB = oldValue - newValue
endif
if DDB < 0 then
    DDB = 0
endif


  was:
Add Constraints:
cost >= 0, salvage >= 0, salvage <= cost, period >= 1, period <= lifeTime, declinationFactor > 0

Change formula according to http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_DDB_function

To calculate depreciation, DDB uses a fixed rate. When declinationFactor = 2 this is the double-declining-balance method (because it is double the straight-line rate that would depreciate the asset to zero). The rate is given by:

    rate = declinationFactor / lifeTime

The depreciation each year is calculated as

    depreciation_of_year = MINIMUM( book_value_at_start_of_year * rate; book_value_at_start_of_year - salvagevalue )

Thus the asset depreciates at rate until the book value is salvagevalue.

book_value_at_start_of_year_i = originalcost - sigma_i(1,year_i-1,depreciation_of_year_i)


The current formula is missing the MINIMUM evaluation and uses only (book_value_at_start_of_year * rate), hence the TODO in the draft.

Alternative algorithm:

rate = declinationFactor / lifeTime
if rate >= 1 then
    rate = 1
    if period == 1 then
        oldValue = cost
    else
        oldValue = 0
    endif
else
    oldValue = cost * pow( 1 - rate, period - 1 )
endif
newValue = cost * pow( 1 - rate, period )
if newValue < salvage then
    DDB = oldValue - salvage
else
    DDB = oldValue - newValue
endif
if DDB < 0 then
    DDB = 0
endif



Calc, Excel, Gnumeric and KSpread all evaluate non-integer values as period arguments. The current definition and formula is wrong and is to be replaced by the second algorithm given in the Proposal field.

> DDB: add formula
> ----------------
>
>                 Key: OFFICE-2222
>                 URL: http://tools.oasis-open.org/issues/browse/OFFICE-2222
>             Project: OASIS Open Document Format for Office Applications (OpenDocument) TC
>          Issue Type: Sub-task
>          Components: OpenFormula
>    Affects Versions: ODF 1.2
>            Reporter: Eike Rathke
>            Assignee: Eike Rathke
>             Fix For: ODF 1.2
>
>
> Broken out from OFFICE-916
> DDB lacks a mathematical formula.
> [ed.]
> It does not lack a formula (anymore?), but the current formula is incomplete.
> Constraints are not given.
> TODO: A different algorithm may be used that allows for non-integer periods. OOoCalc uses it, compare with Excel, Gnumeric and KSpread.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://tools.oasis-open.org/issues/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira