I need to calculate amounts over time, i.e. I have a start date (1 July 07)
of a lease and an end date (30 June 2019). my problem is I have to calculate
commitments as at May 08 over 3 intervals.
1 year = May 08-Apr 09 equals amount X and
2-5 years = May 09-April 2014 equals amount Y
5 years = May 2014 -June 2019 equals amount Z AND
the yearly escalation should also be taken into account.
Lease at start = 7,000 escalation = 9%
Can anybody help me with a formula??????

Let's make a yearly expense table:

In A1 thru A11 enter 1 thru 11
In B1 enter:
5/1/2008 4/30/2009
5/1/2009 4/30/2010
5/1/2010 4/30/2011
5/1/2011 4/30/2012
5/1/2012 4/30/2013
5/1/2013 4/30/2014
5/1/2014 4/30/2015
5/1/2015 4/30/2016
5/1/2016 4/30/2017
5/1/2017 4/30/2018
5/1/2018 4/30/2019

In D1 enter 700; in D2 enter:
=D1*1.09 and copy down

A1 thru D11 show:
1 5/1/2008 4/30/2009 7,000.00
2 5/1/2009 4/30/2010 7,630.00
3 5/1/2010 4/30/2011 8,316.70
4 5/1/2011 4/30/2012 9,065.20
5 5/1/2012 4/30/2013 9,881.07
6 5/1/2013 4/30/2014 10,770.37
7 5/1/2014 4/30/2015 11,739.70
8 5/1/2015 4/30/2016 12,796.27
9 5/1/2016 4/30/2017 13,947.94
10 5/1/2017 4/30/2018 15,203.25
11 5/1/2018 4/30/2019 16,571.55



Finally in E1:
=D1 the first yearly expense
In E2 enter:
=SUM(D2:D5) expense for years 2,3,4,5
In E3 enter:
=SUM(D6:D11) expenses for the following years

For your data, the three parts a

7,000.00
34,892.97
81,029.08


--
Gary''s Student - gsnu200791


"Desperatesusy" wrote:

I need to calculate amounts over time, i.e. I have a start date (1 July 07)
of a lease and an end date (30 June 2019). my problem is I have to calculate
commitments as at May 08 over 3 intervals.
1 year = May 08-Apr 09 equals amount X and
2-5 years = May 09-April 2014 equals amount Y
5 years = May 2014 -June 2019 equals amount Z AND
the yearly escalation should also be taken into account.
Lease at start = 7,000 escalation = 9%
Can anybody help me with a formula??????

Thanks, but this does not help me much, coz
1. the data overlaps, i.e. the lease starts in July 07, but I need to
calculate it from May 08
2. I have 500 rows of leases - is there no simpler solution apart from
making a table on each and every lease?

"Gary''s Student" wrote:

Let's make a yearly expense table:

In A1 thru A11 enter 1 thru 11
In B1 enter:
5/1/2008 4/30/2009
5/1/2009 4/30/2010
5/1/2010 4/30/2011
5/1/2011 4/30/2012
5/1/2012 4/30/2013
5/1/2013 4/30/2014
5/1/2014 4/30/2015
5/1/2015 4/30/2016
5/1/2016 4/30/2017
5/1/2017 4/30/2018
5/1/2018 4/30/2019

In D1 enter 700; in D2 enter:
=D1*1.09 and copy down

A1 thru D11 show:
1 5/1/2008 4/30/2009 7,000.00
2 5/1/2009 4/30/2010 7,630.00
3 5/1/2010 4/30/2011 8,316.70
4 5/1/2011 4/30/2012 9,065.20
5 5/1/2012 4/30/2013 9,881.07
6 5/1/2013 4/30/2014 10,770.37
7 5/1/2014 4/30/2015 11,739.70
8 5/1/2015 4/30/2016 12,796.27
9 5/1/2016 4/30/2017 13,947.94
10 5/1/2017 4/30/2018 15,203.25
11 5/1/2018 4/30/2019 16,571.55



Finally in E1:
=D1 the first yearly expense
In E2 enter:
=SUM(D2:D5) expense for years 2,3,4,5
In E3 enter:
=SUM(D6:D11) expenses for the following years

For your data, the three parts a

7,000.00
34,892.97
81,029.08


--
Gary''s Student - gsnu200791


"Desperatesusy" wrote:

I need to calculate amounts over time, i.e. I have a start date (1 July 07)
of a lease and an end date (30 June 2019). my problem is I have to calculate
commitments as at May 08 over 3 intervals.
1 year = May 08-Apr 09 equals amount X and
2-5 years = May 09-April 2014 equals amount Y
5 years = May 2014 -June 2019 equals amount Z AND
the yearly escalation should also be taken into account.
Lease at start = 7,000 escalation = 9%
Can anybody help me with a formula??????