Forecast payments over 24 months

[gk759]

Hi,

I have the task of creating an excel formula to spread total value for each contract over 24 month columns....i.e 2010 and 2011. Based on start date. The amount needs to be spread Monthly,quarterly, semi-annually or yearly dependng on the terms of the contract.

I am not sure if this can be accomplished within the excel boundaries with VBA or a formula.I tried to look up forums, vba excel books etc....but couldn't come across something relevant to steer me in the right direction. Pasted below is a sample of the data.

Contract Start Date Amount Type

A 1/20/2010 $1000 Quarterly

B 2/10/2010 $2000 Semi Annually

C 4/01/2010 $3000 Monthly

D 3/23/2010 $5000 Yearly

Thoughts?

Thanks in advance.

Gary

 

[SkipVought]

Hi,

1. Set up a table to describe the cycle and payment frequency
[tt]
TypeList Cycle Pmts
Quarterly 3 4
Semi Annually 6 2
Yearly 12 1
Monthly 1 12
[/tt]
Name the ranges using Insert > Name > Create -- Create names in TOP row

2. the DATE row:
[tt]
E1: =DATE(YEAR(MIN(Start_Date)),MONTH(MIN(Start_Date)),1)
F1: =DATE(YEAR(E1),MONTH(E1)+1,1)
[/tt]
and copy across.

2. The formula
[tt]
E2: =IF(AND(E$1>=DATE(YEAR($B2),MONTH($B2),1),F$1<=DATE(YEAR($B2)+1,MONTH($B2)+1,0)),$C2/INDEX(Pmts,MATCH($D2,TypeList,0),1),0)*IF(MOD(MONTH(E$1)-MONTH($B2),INDEX(Cycle,MATCH($D2,TypeList,0),1))=0,1,0)
copy down and across thru all data rows and date columns.
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[gk759]

Thanks Skip. I'll try it and let you know.

Gary