pls help again!

[Guest]

Thanks for the help Roger. It worked......also, how can i use this function
if there are more than 7 installments to be paid?

Shirley

Hi Shirley

Assuming you enter proper Excel Dates in D1 onward, e.g. 01 Feb 2006
formatted to just show Month and year with
Format>Cells>Number>Custom>mmmyy
Also, assuming you use a proper Excel date in column B, then enter the
following formula into cell D2 and copy across as far as required.
Copy the whole row down as far as required.
=IF($A3="","",IF(D$1<$B3,"",
IF(DATEDIF($B3,D$1,"m")=0,$C3*50%,
IF(DATEDIF($B3,D$1,"m")=5,$C3*25%,
IF(DATEDIF($B3,D$1,"m")=10,$C3*25%,"")))))

Until you enter a value greater than 0 in your flag field, the values
will remain as 0
If you need more help on the undocumented Datedif function, take a look
at Chip Pearson's site
http://www.cpearson.com/excel/datedif.htm

 

[Dav]

As you have set it up there are only 3 instalments as you have choose
50% 25% and 25% of the total as the repayments. When do you want th
seven repayments to be made and what percentage of the total are they?

Regards

Da

 

[Roger Govier]

Hi Shirley

Just a small point, it would have been better to stay in the same
thread, rather than post a new thread. Luckily, I spotted this request
also.
As Dav has said, we need more information about when these instalments
will take place, and the amounts per instalment.
What is going to be the "trigger" that determines one is 3 instalments,
another is 4 or 5 or 7? Will it be the flag?

 

[Guest]

Dav,

assume that the 2nd payment is 75% instead of the 25% that i gave in the
example and that this 75% has to be paid in 8 equal quarterly installments. I
hope its clear...

Thanks

 

[Guest]

Hi Roger

ok. there are three overall installments to be made, 20%, 75% and 5%. Now
according to the formula, if the sale takes place in Mar-06, the first
installment of 20% of sale will show up under column Mar-06. The 75% has to
be split into 8 part installments payable every three months after Mar-06.
The final 5% will be paid 3 months after the last of the 8 part-installments
is completed. hope this makes things a little clearer

Shirley

 

[Roger Govier]

Hi Shirley

OK, so lets see if we can come up with a generic solution.
My assumptions.
1. All agreements have a deposit paid (of varying percentages) at the
same date as in column B.
2. All agreements have a number of repayments at varying frequency of
varying percentages.
3. All agreements have a terminal payment, made at the same frequency
after the last regular payment as above, but this terminal payment can
be set to 0%.

With the above, I set up a table which I named as Rates holding the
following data ( I am showing the headings going down the page to avoid
email wrap, but they were set up going across the page)
A: Total No. Payments
B: Deposit (%)
C: Frequency (months)
D: Instalment (%)
E: Terminal (%)

So this latest request would have values of 8, 20%, 3, 12.5%, 5%
Your original request would have values of 4, 50%, 5, 25.0%, 0%

With the above table set up (add as many more types as you wish, as long
as they conform to the above) then the formula becomes a fairly
horrendous
=IF(ISERROR(VLOOKUP($A2,Rates,1,0)),"",
IF(D$1<$B2,"",
IF(DATEDIF($B2,D$1,"m")>($A2-1)*VLOOKUP($A2,Rates,3,0),"",
IF(DATEDIF($B2,D$1,"m")=0,$C2*VLOOKUP($A2,Rates,2,0),
IF(DATEDIF($B2,D$1,"m")=($A2-1)*VLOOKUP($A2,Rates,3,0),$C2*VLOOKUP($A2,Rates,5,0),
IF(MOD(DATEDIF($B2,D$1,"m"),VLOOKUP($A2,Rates,3,0))=0,$C2*VLOOKUP($A2,Rates,4,0),
""))))))

I have assumed that you will enter the Total No. of Payments in the Flag
column (A) of your results table.
Basically the formula says
If no values in table for that No. of Months does not exist, then
nothing
If Month in row 1 is before start date then nothing
If number of months is greater than the total duration of contract, then
nothing
If month equals starting month, then loan * deposit percentage
If number of months equals last month of contact, then loan * terminal
percentage
If month is a multiple of frequency then loan * instalment percentage
Otherwise nothing.

Note that you have to take 1 away from the total number of payments, as
the initial deposit uses 1 of the counts.

Hopefully from the above you will be able to deal with any type of
contact that you have.

 

[Guest]

Hi Roger

Thanks for all the effort and help. I appreciate it. I will try this out
and see if it works.

Regards
Shirley