Reducing Balance Depreciation: X = N * (1-((1-r)^(1/12))) for 1 mo's book-val. change

[owen_legend]

I'm trying to figure out how (if possible) to work backwards on reducing balance depreciation - For reference, I'm an accountant and not a maths expert:

So i've formed the equation to calculate 1 months depreciation charge from a net book value:

X = N * (1-((1-r)^(1/12)))

Where
X = The next months charge
N = Current Net Book Value
r = Annual % rate as a decimal

What I want to do is to figure out how to get back to the previous months Net Book Value from this.

To get the next Net Book Value, the formula is:

N2 = N-(N * (1-((1-r)^(1/12))))

Where
N2 = Future Net Book Value
N = Current Net Book Value
r = Annual % rate as a decimal

Just in case my description of my problem doesn't make sense - I have provided the following example:

Current Net Book Value = £14237.51

Applying the first formula described in my post where the annual % rate is 15%

X = 14237.51 * (1-((1-0.15)^(1/12))) = 191.52

So the future Net Book Value will be £14045.99 - then the next one will be £13857.04

I want to calculate the Net Book Value before £14237.51

Thanks in advance!

 

[tkhunny]

I'm trying to figure out how (if possible) to work backwards on reducing balance depreciation - For reference, I'm an accountant and not a maths expert:

So i've formed the equation to calculate 1 months depreciation charge from a net book value:

X = N * (1-((1-r)^(1/12)))

Where
X = The next months charge
N = Current Net Book Value
r = Annual % rate as a decimal

What I want to do is to figure out how to get back to the previous months Net Book Value from this.

To get the next Net Book Value, the formula is:

N2 = N-(N * (1-((1-r)^(1/12))))

Where
N2 = Future Net Book Value
N = Current Net Book Value
r = Annual % rate as a decimal

Just in case my description of my problem doesn't make sense - I have provided the following example:

Current Net Book Value = £14237.51

Applying the first formula described in my post where the annual % rate is 15%

X = 14237.51 * (1-((1-0.15)^(1/12))) = 191.52

So the future Net Book Value will be £14045.99 - then the next one will be £13857.04

I want to calculate the Net Book Value before £14237.51

Thanks in advance!

One piece at time.

r = Annual Rate of Depreciation -- This is a funny thing. After calculating 12 months at this annual rate, you will NOT depreciate r * N. Thought Question: Why?

1 - r = Over a period of one year, this is the fraction that is NOT depreciated.

(1 - r)^(1 / 12) = Over a period of one month, this is the fraction that is NOT depreciated.

1 - [(1 - r)^(1 / 12)] = Over a period of one month, this is the fraction that IS depreciated.

N * {1 - [(1-r)^(1/12)]} = Over a period of one month, this is the AMOUNT that is depreciated.

 

[Denis]

I'm not sure what you're asking.

Let's say we look at a 1 year period, beginning with 15,000 depreciating
at 15% annually cpd. monthly, so monthly factor = -.15/12 = -.0125

So is this what will happen:

MONTH  DEPRECIATION    BALANCE
  0                   15,000.00
  1      187.50       14,812.50
  2      185.16       14,627.34
....
 11      165.34       13,061.69
 12      163.27       12,898.42

Future value formula: f = a(1 + r)^n
a = 15,000
r = -.0125
n = 12

f = 15000*(1-.0125)^12 = 12898.42

Please confirm....

 

[owen_legend]

One piece at time.

r = Annual Rate of Depreciation -- This is a funny thing. After calculating 12 months at this annual rate, you will NOT depreciate r * N. Thought Question: Why?

1 - r = Over a period of one year, this is the fraction that is NOT depreciated.

(1 - r)^(1 / 12) = Over a period of one month, this is the fraction that is NOT depreciated.

1 - [(1 - r)^(1 / 12)] = Over a period of one month, this is the fraction that IS depreciated.

N * {1 - [(1-r)^(1/12)]} = Over a period of one month, this is the AMOUNT that is depreciated.

EDITED POST - NOW SOLVED, SEE MESSAGE AT END OF POST

The formulas and descriptions here are spot on.

So maybe I should further explain my issue and why I am not just depreciating r * N. It was late in the day when I wrote this question and I've had some time to think more.

I have an asset register that populates automatically from an accounting software into an excel document. I am using it to calculate actual depreciation amounts per month (amongst other things) as I report on this information monthly.

Some assets are acquired within the year and so a full years depreciation will not be calculated as my year runs from Jan-Dec.


The data I get from my accounting software will give me a snapshot at the current month - The relevant fields I can get are:
Cost Price
Current Net Book Value
Depreciation charged to date (Difference between cost price & Net Book Value)
Last Months Depreciation
Next Months Depreciation
Annual Depcn Rate
Purchase Date

For any given asset at whatever month i'm generating the data, I need to be able to calculate any given months depreciation.


If an asset was purchased on 31 March for 15000 depreciating at 15% annually and I am generating the data in August - what was the monthly charge for June? No decpn will be charged in March.

I could of course work through the formula month by month, calculating the net book value for every month, but i would rather a formula that can do it all given that the months passed since acquisition = 3

[TABLE="width: 192"]
[TR]
[TD="width: 64, align: right"]0[/TD]
[TD="width: 64"][/TD]
[TD="width: 64, align: right"]15000[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]201.7792[/TD]
[TD="align: right"]14798.22[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]199.0649[/TD]
[TD="align: right"]14599.16[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]196.3871[/TD]
[TD="align: right"]14402.77[/TD]
[/TR]
[/TABLE]

The monthly charge in June (Month 3) is 196.3871


I feel like i'm beginning to ramble - does this make sense what i'm trying to do?





EDIT - Just realised that to achieve what i'm looking to do, I can substitute the 1 in (1/12) for x/12 depending on how many months have passed.
Thanks for the responses!