formula to count number of months that have passed

[Guest]

I'm trying to build a depreciation schedule for assets, in which depreciation
is calculated on a monthly basis.

I have my table set up so that the column headings are months, i.e,,
10/31/2006, 11/30/2006, etc.

Each row in the table is devoted to a particular asset, which asset has an
asset creation date, and the difference between the asset creation date and
the month in question would be the number of months over which depreciation
has accumulated for the asset in question.

Example: Asset A was created on 9/26/2006. So, for October, 2006,
depreciation would be 1 month of depreciation. The depreciable life of the
asset is 48 months. Now, I've figured out a formula that allows me to
calculate depreciation in this manner through the end of 2006. But when
1/31/2007 comes around I start to get errors. Here's the formula I'm using:

=IF($F7<0,"N/A",(MONTH(N$6)-MONTH($G7))/$B$3*$F7)

F7 is the asset's cost, N6 is the column heading showing the relevant month
(in this case, 10/31/2006), G7 is the asset creation date (9/22/2006), B3 is
the depreciable life of the asset (48 months).

So, here's the problem: when I get to 1/31/2006 (cell Q6), the difference
between that month and the month in 9/22/2006 is -8, which gives me absurd
depreciation results. Can someone figure out a way to tell Excel I'm
interested in counting months? The jerry-rigged way I have it here obviously
doesn't work when the year turns.

Thanks,

Dave

 

[Bob Phillips]

=IF($F7<0,"N/A",(YEAR(N$6)-YEAR($G7))*12+MONTH(N$6)-MONTH($G7))/$B$3*$F7

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Actually, I've answered this question myself:
=IF($F7<0,"N/A",DATEDIF($G7,N$6,"m")/$B$3*$F7) calculates correctly.

Dave

 

[Guest]

Wow, that's an interesting way to solve this, thanks.

Dave

 

[Guest]

More ways to skin cats in Excel than there cats in Excel.

 

[Bob Phillips]

Actually, the way is not Excel, it is just simple math. Excel is just the
vehicle for that math.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)