Payment based on turnover assumptions

[John Braly]

Hi all:

I have a question with a formula to calculate payment based on % of drawdown on a loan in a month and based on different collection cycles:

A1: Start date
A2: End date
A3: Down payment (% of total loan) @ start date
A4: Payment calculation date (every X days)
A5: Payment collection date (after A4 every Y days)
A6: Total collection payment = (1-A3-A7)
A7: Final payment (% of total loan)
A8: Date for final payment (Z days after A2)
A9: Total loan

B1 to G1 display monthly months

B2 to G2 are the amounts of drawn down in each month that customers can take. Total % = 1

So I want a formula to display the amount I collect on a monthly basis.

Example:
B1 is start date and G1 is end date (Jan through June)

B2 to G2 is the % of total loan a customer draws in each month

then I want a formula to display:
a) B3:G3 -- Display payment date (B1 +A4*(number of cycle) +A5 in the according month (If a payment date falls in a month, say February, then C3 displays this date)

b) B4 = A3*A9
c) Then it calculates the amount drawn down at every B1 + A4*(number of cycle) [% is pro-rated on monthly draw down in B2:G2 based on days], and then display [this % * A6 * A9] at every B1 + A4*(number of cycle) + A5 (in C4 through G4 and beyond if needed). It means there will only numbers in this row wherever B3:G3 has a date.

d) Display [A7 * A9] in the month column of [A2+A8]

I hope I made it as clear as possible since this can be confusing. If anyone can help me it'd be greatly appreciated, since I have 100+ schedules to work and doing manual work just seems impossible...

Thank you everyone!

 

[John Braly]

Can someone please help me with this? Please let me know if it's too confusing and I'll try to post up a screen of a the file I'm working with?

Thank you so much.