I have a timeseries of data at a monthly frequency starting in Jan-2011 to Dec-2050 (480 months) in one row. The rows below it have 10 tenants (so 10 rows) with amounts in any given months. Some months contain $0. I am trying to create a new schedule which amortizes these amounts based on a defined interest rate and amortization period (i.e. PMT(0%,60,-100000,0)).
It may be clearer if I show an example below:
Jan-2011 Feb-2011 Mar-2011 ... Dec-2050
Tenant 1 60 Months $10,000 $0 $25,000 $0
Tenant 2 90 Months $50,000 $12,000 $0 $0
Tenant 3 60 Months $0 $0 $0 $0
...
Tenant 10 120 Months $10,000 $0 $15,000 $18,000
The new schedule needs to amortize these amounts over the specified amortization period. So for Tenant 1, assuming an interest rate of 0%, just to keep the math simple, the schedule should look as follows:
Jan-2011 Feb-2011 Mar-2011 ... Dec-2050
Tenant 1 60 Months $166.67 $166.67 $583.33 $0
Hopefully my question makes sense.
Thanks
It may be clearer if I show an example below:
Jan-2011 Feb-2011 Mar-2011 ... Dec-2050
Tenant 1 60 Months $10,000 $0 $25,000 $0
Tenant 2 90 Months $50,000 $12,000 $0 $0
Tenant 3 60 Months $0 $0 $0 $0
...
Tenant 10 120 Months $10,000 $0 $15,000 $18,000
The new schedule needs to amortize these amounts over the specified amortization period. So for Tenant 1, assuming an interest rate of 0%, just to keep the math simple, the schedule should look as follows:
Jan-2011 Feb-2011 Mar-2011 ... Dec-2050
Tenant 1 60 Months $166.67 $166.67 $583.33 $0
Hopefully my question makes sense.
Thanks
