formula for future value

**associates** · 07-06-2010, 01:22 AM

Hi,

I was wondering if I could get some help here with using built in formulas.

I am looking for (if any) a built in function that would work out the future value after depreciation over period of time. The variables that I know are the purchase item price, the depreciation rate (18.7%) and the period of time since I bought the item. This is the variables I have at hand.

Any help would be greatly appreciated.

Thank you in advance

**davesexcel** · 07-06-2010, 01:51 AM

Try this
=FV(
then click the Function wizard (fx) for help

**Rahul Nagar** · 07-06-2010, 01:52 AM

Hello,

You can use DB formula.

DB = Returns the depreciation of the asset for a specified period using the fixed-declining balance method.

Regards
Rahul

**Trebor76** · 07-06-2010, 02:01 AM

Hi associaties,

Not sure if this is what you're after, but here goes.

Assuming a scrap value of zero and that the stratight line method of depreciation is used (see also the SLN function), for an asset of say $25K the annual depreciation expense under a rate of 18.7% would be $4,675 i.e. =25000/(100/18.7). This would take 5.35 years =100/18.7 to write down to zero.

HTH

Robert

**associates** · 07-06-2010, 02:34 AM

Hi,

Thank you all for your replies.

I have tried DB as well as SLN functions but could not get the right values. The values I got do not match that of our accountant's.

Here is the case,

We bought a car worth of $34879 on the 1st of April, 2005. Our end of financial year is on the 30th of June every year. The depreciation value for the year 2005 is $1631. This makes the value of the car become $33248. Then, for the year 2005/2006, the depreciation value is $6234 and the car's value after depreciation is $27014.

The only information the accountant gave us is the depreciation rate is 18.7% per annum.

Hope the above example would help you to help me figure which formula I should use to match the accountant's figures for every year.

Thank you in advance

**Trebor76** · 07-06-2010, 03:06 AM

Hi associaties,

Gee, you'd think he/she would provide a schedule to back up how they've calculated their figures

Anyways for the first financial year end the $1,631 has been found by this formula (or something very similar):

=(3/12)*34879/(100/18.7)

Note the 3/12 represents the 3 months from when the car was bought (1 Apr) till the end of the financial year (30 Jun).

I then get a slightly different depreciation charge for the 2005/06 financial year of $6,217 i.e. =33248/(100/18.7) which in turn leaves a slightly different WDV of $27,031.

HTH

Robert

**associates** · 07-06-2010, 03:25 AM

Hi Robert,

Thank you for your reply.

Yes, I agree with you. I have been scratching my head or banging my head for as well but more than once

. I got the first bit right but not for the second bit (similar to your figure). If we use the last closing figure for the year, we can then get something closer to their figure. However, in excel spreadsheet, I'd try not to change the purchase price at all. I suppose what i wanted to do is by changing the period of time, we get the similar value as that of the accountant's. The purchase price is fixed variable and so is the depreciation rate (18.7%). The only variable that changes is the period of time from the time we bought it.

I have no more idea of coming up with the formula or solution to this.

Any help would be appreciated. Thank you Robert for trying out.

Thank you