Hi

I am trying to calculate depreciation in my Excel spreadsheet. The
depreciation is over 60 months, so for a $6,000 item, the formula is
=$6,000/60 = $100

The problem I am encountering is making that calculation stop after 60
months. So in month 61, the cell reads $0, instead of $100. Is there anyway
in Excel to 'time limit' the effectiveness of a cell's formula?

To make it even more complicated, I will have ongoing items for
depreciation, so I will need to add new items to the formula, while
simultaneously letting existing items expire, as above.

It's pretty hard to explain, so I imagine it will be pretty hard to solve,
but I would certainly appreciate any advice.

Cheers,
Link.

Let's say your initial value is in A1 and the length of time is in A2. I'm
also assuming that after the length listed, the value is equal to zero.
Let's also assume that the # of months since the initial value is A3.

=if(A3=A2,(A1*A3)/A2,0)

I think that's what you want.

HTH,
Barb Reinhardt

"Link" wrote:

Hi

I am trying to calculate depreciation in my Excel spreadsheet. The
depreciation is over 60 months, so for a $6,000 item, the formula is
=$6,000/60 = $100

The problem I am encountering is making that calculation stop after 60
months. So in month 61, the cell reads $0, instead of $100. Is there anyway
in Excel to 'time limit' the effectiveness of a cell's formula?

To make it even more complicated, I will have ongoing items for
depreciation, so I will need to add new items to the formula, while
simultaneously letting existing items expire, as above.

It's pretty hard to explain, so I imagine it will be pretty hard to solve,
but I would certainly appreciate any advice.

Cheers,
Link.

Hi

Assuming you set out your schedule as follows
A Asset Name
B Purchase Date
C Asset Value
D Number of Months to Depreciate
E End of starting month e.g 31 Jan 2006
Format cell E, Format CellsNumberCustom mmm yy so it displays as Jan
06
Drag cell E1 across the screen, holding down the fill handle, on
releasing the mouse button, choose Fill Months

Now in cell E2 enter
=IF($A2="","",IF(AND(E$1=$B2,E$1<=DATE(YEAR($B2), MONTH($B2)+$D2,DAY($B2))),$C2/$D2,0))
and copy across, and down

If you have the Analysis Toolpak loaded, ToolsAddinscheck Analysis
Toolpak, then you could use the shorter formula
=IF($A2="","",IF(AND(E$1=$B2,E$1<=EOMONTH($B2,$D2-1)),$C2/$D2,0))
--
Regards

Roger Govier


"Link" wrote in message
...
Hi

I am trying to calculate depreciation in my Excel spreadsheet. The
depreciation is over 60 months, so for a $6,000 item, the formula is
=$6,000/60 = $100

The problem I am encountering is making that calculation stop after 60
months. So in month 61, the cell reads $0, instead of $100. Is there
anyway in Excel to 'time limit' the effectiveness of a cell's formula?

To make it even more complicated, I will have ongoing items for
depreciation, so I will need to add new items to the formula, while
simultaneously letting existing items expire, as above.

It's pretty hard to explain, so I imagine it will be pretty hard to
solve, but I would certainly appreciate any advice.

Cheers,
Link.

Hi Roger

I tried what you said. Firstly I couldn't find the option to choose Fill
Months upon releasing my mouse button after dragging. Then I got these
results:

Asset Name Purchase Date Asset Value Number of Months to depreciate
End of Starting Month
StudyStation Jun-07 $10,000 60 Jan-00


Changing the number of months to depreciate, has no affect on the date. I'm
just not sure what is supposed to be going on there.

I need to find the ending date of the depreciation, but I also need a column
to show the monthly amount and it should return to zero after the
depreciation time.

Would you be kind enough to expand further, please. I really appreciate it.

Cheers,
Link.

"Roger Govier" wrote in message
...
Hi

Assuming you set out your schedule as follows
A Asset Name
B Purchase Date
C Asset Value
D Number of Months to Depreciate
E End of starting month e.g 31 Jan 2006
Format cell E, Format CellsNumberCustom mmm yy so it displays as Jan 06
Drag cell E1 across the screen, holding down the fill handle, on releasing
the mouse button, choose Fill Months

Now in cell E2 enter
=IF($A2="","",IF(AND(E$1=$B2,E$1<=DATE(YEAR($B2), MONTH($B2)+$D2,DAY($B2))),$C2/$D2,0))
and copy across, and down

If you have the Analysis Toolpak loaded, ToolsAddinscheck Analysis
Toolpak, then you could use the shorter formula
=IF($A2="","",IF(AND(E$1=$B2,E$1<=EOMONTH($B2,$D2-1)),$C2/$D2,0))
--
Regards

Roger Govier


"Link" wrote in message
...
Hi

I am trying to calculate depreciation in my Excel spreadsheet. The
depreciation is over 60 months, so for a $6,000 item, the formula is
=$6,000/60 = $100

The problem I am encountering is making that calculation stop after 60
months. So in month 61, the cell reads $0, instead of $100. Is there
anyway in Excel to 'time limit' the effectiveness of a cell's formula?

To make it even more complicated, I will have ongoing items for
depreciation, so I will need to add new items to the formula, while
simultaneously letting existing items expire, as above.

It's pretty hard to explain, so I imagine it will be pretty hard to
solve, but I would certainly appreciate any advice.

Cheers,
Link.

Hi

I should have said hold down the right mouse button as you drag, then on
release of button choose Fill months.

Secondly, it looks as though you have cell E2 formatted as Date, it
should be Number, with as many places as your require.
What you should then see, assuming you have 31/12/07 in E1, is
0,0,0,0,0,166.7,166.7,166.7 etc.
in cells E2:L2

If you are still having difficulty, let me have your email address and I
will send you a sheet already set up.
--
Regards

Roger Govier


"Link" wrote in message
...
Hi Roger

I tried what you said. Firstly I couldn't find the option to choose
Fill Months upon releasing my mouse button after dragging. Then I got
these results:

Asset Name Purchase Date Asset Value Number of Months to
depreciate End of Starting Month
StudyStation Jun-07 $10,000 60 Jan-00


Changing the number of months to depreciate, has no affect on the
date. I'm just not sure what is supposed to be going on there.

I need to find the ending date of the depreciation, but I also need a
column to show the monthly amount and it should return to zero after
the depreciation time.

Would you be kind enough to expand further, please. I really
appreciate it.

Cheers,
Link.

"Roger Govier" wrote in message
...
Hi

Assuming you set out your schedule as follows
A Asset Name
B Purchase Date
C Asset Value
D Number of Months to Depreciate
E End of starting month e.g 31 Jan 2006
Format cell E, Format CellsNumberCustom mmm yy so it displays as
Jan 06
Drag cell E1 across the screen, holding down the fill handle, on
releasing the mouse button, choose Fill Months

Now in cell E2 enter
=IF($A2="","",IF(AND(E$1=$B2,E$1<=DATE(YEAR($B2), MONTH($B2)+$D2,DAY($B2))),$C2/$D2,0))
and copy across, and down

If you have the Analysis Toolpak loaded, ToolsAddinscheck Analysis
Toolpak, then you could use the shorter formula
=IF($A2="","",IF(AND(E$1=$B2,E$1<=EOMONTH($B2,$D2-1)),$C2/$D2,0))
--
Regards

Roger Govier


"Link" wrote in message
...
Hi

I am trying to calculate depreciation in my Excel spreadsheet. The
depreciation is over 60 months, so for a $6,000 item, the formula is
=$6,000/60 = $100

The problem I am encountering is making that calculation stop after
60 months. So in month 61, the cell reads $0, instead of $100. Is
there anyway in Excel to 'time limit' the effectiveness of a cell's
formula?

To make it even more complicated, I will have ongoing items for
depreciation, so I will need to add new items to the formula, while
simultaneously letting existing items expire, as above.

It's pretty hard to explain, so I imagine it will be pretty hard to
solve, but I would certainly appreciate any advice.

Cheers,
Link.

assuming you have 31/12/07 in E1

That should have read
assuming you have 31/01/07 in E1

(my dates are UK format)

Also, I didn't comment upon your statement
Changing the number of months to depreciate, has no affect on the date

It doesn't, and is not supposed to. It just works out when to end
depreciation, and the amount of depreciation to charge per month.
--
Regards

Roger Govier


"Roger Govier" wrote in message
...
Hi

I should have said hold down the right mouse button as you drag, then
on release of button choose Fill months.

Secondly, it looks as though you have cell E2 formatted as Date, it
should be Number, with as many places as your require.
What you should then see, assuming you have 31/12/07 in E1, is
0,0,0,0,0,166.7,166.7,166.7 etc.
in cells E2:L2

If you are still having difficulty, let me have your email address and
I will send you a sheet already set up.
--
Regards

Roger Govier


"Link" wrote in message
...
Hi Roger

I tried what you said. Firstly I couldn't find the option to choose
Fill Months upon releasing my mouse button after dragging. Then I got
these results:

Asset Name Purchase Date Asset Value Number of Months to
depreciate End of Starting Month
StudyStation Jun-07 $10,000 60 Jan-00


Changing the number of months to depreciate, has no affect on the
date. I'm just not sure what is supposed to be going on there.

I need to find the ending date of the depreciation, but I also need a
column to show the monthly amount and it should return to zero after
the depreciation time.

Would you be kind enough to expand further, please. I really
appreciate it.

Cheers,
Link.

"Roger Govier" wrote in message
...
Hi

Assuming you set out your schedule as follows
A Asset Name
B Purchase Date
C Asset Value
D Number of Months to Depreciate
E End of starting month e.g 31 Jan 2006
Format cell E, Format CellsNumberCustom mmm yy so it displays as
Jan 06
Drag cell E1 across the screen, holding down the fill handle, on
releasing the mouse button, choose Fill Months

Now in cell E2 enter
=IF($A2="","",IF(AND(E$1=$B2,E$1<=DATE(YEAR($B2), MONTH($B2)+$D2,DAY($B2))),$C2/$D2,0))
and copy across, and down

If you have the Analysis Toolpak loaded, ToolsAddinscheck Analysis
Toolpak, then you could use the shorter formula
=IF($A2="","",IF(AND(E$1=$B2,E$1<=EOMONTH($B2,$D2-1)),$C2/$D2,0))
--
Regards

Roger Govier


"Link" wrote in message
...
Hi

I am trying to calculate depreciation in my Excel spreadsheet. The
depreciation is over 60 months, so for a $6,000 item, the formula
is =$6,000/60 = $100

The problem I am encountering is making that calculation stop after
60 months. So in month 61, the cell reads $0, instead of $100. Is
there anyway in Excel to 'time limit' the effectiveness of a cell's
formula?

To make it even more complicated, I will have ongoing items for
depreciation, so I will need to add new items to the formula, while
simultaneously letting existing items expire, as above.

It's pretty hard to explain, so I imagine it will be pretty hard to
solve, but I would certainly appreciate any advice.

Cheers,
Link.

I think I am finally getting somewhere now Rog.

Thanks for your perseverance. I just have two more questions (please):

It seems to go for one month too many. In the following example, the
payments go for five months instead of four, and include one extra payment:

Asset Name Purchase Date Asset Value Number of Months to depreciate
Jun-07 Jul-07 Aug-07 Sep-07 Oct-07
StudyStation Jun-07 $10,000 4 2500 2500 2500 2500 2500


What happens if I have another asset, say in month three? How can I include
that asset in the depreciation payment, and still have them both exist for
their respective depreciation periods?

Thanks again and I look forward to your reply.

Cheers,
Link.


"Roger Govier" wrote in message
...
assuming you have 31/12/07 in E1

That should have read
assuming you have 31/01/07 in E1

(my dates are UK format)

Also, I didn't comment upon your statement
Changing the number of months to depreciate, has no affect on the date

It doesn't, and is not supposed to. It just works out when to end
depreciation, and the amount of depreciation to charge per month.
--
Regards

Roger Govier


"Roger Govier" wrote in message
...
Hi

I should have said hold down the right mouse button as you drag, then on
release of button choose Fill months.

Secondly, it looks as though you have cell E2 formatted as Date, it
should be Number, with as many places as your require.
What you should then see, assuming you have 31/12/07 in E1, is
0,0,0,0,0,166.7,166.7,166.7 etc.
in cells E2:L2

If you are still having difficulty, let me have your email address and I
will send you a sheet already set up.
--
Regards

Roger Govier


"Link" wrote in message
...
Hi Roger

I tried what you said. Firstly I couldn't find the option to choose Fill
Months upon releasing my mouse button after dragging. Then I got these
results:

Asset Name Purchase Date Asset Value Number of Months to depreciate
End of Starting Month
StudyStation Jun-07 $10,000 60 Jan-00


Changing the number of months to depreciate, has no affect on the date.
I'm just not sure what is supposed to be going on there.

I need to find the ending date of the depreciation, but I also need a
column to show the monthly amount and it should return to zero after the
depreciation time.

Would you be kind enough to expand further, please. I really appreciate
it.

Cheers,
Link.

"Roger Govier" wrote in message
...
Hi

Assuming you set out your schedule as follows
A Asset Name
B Purchase Date
C Asset Value
D Number of Months to Depreciate
E End of starting month e.g 31 Jan 2006
Format cell E, Format CellsNumberCustom mmm yy so it displays as Jan
06
Drag cell E1 across the screen, holding down the fill handle, on
releasing the mouse button, choose Fill Months

Now in cell E2 enter
=IF($A2="","",IF(AND(E$1=$B2,E$1<=DATE(YEAR($B2), MONTH($B2)+$D2,DAY($B2))),$C2/$D2,0))
and copy across, and down

If you have the Analysis Toolpak loaded, ToolsAddinscheck Analysis
Toolpak, then you could use the shorter formula
=IF($A2="","",IF(AND(E$1=$B2,E$1<=EOMONTH($B2,$D2-1)),$C2/$D2,0))
--
Regards

Roger Govier


"Link" wrote in message
...
Hi

I am trying to calculate depreciation in my Excel spreadsheet. The
depreciation is over 60 months, so for a $6,000 item, the formula is
=$6,000/60 = $100

The problem I am encountering is making that calculation stop after 60
months. So in month 61, the cell reads $0, instead of $100. Is there
anyway in Excel to 'time limit' the effectiveness of a cell's formula?

To make it even more complicated, I will have ongoing items for
depreciation, so I will need to add new items to the formula, while
simultaneously letting existing items expire, as above.

It's pretty hard to explain, so I imagine it will be pretty hard to
solve, but I would certainly appreciate any advice.

Cheers,
Link.