Date formula for a loan

[kristina_q]

I am trying to recreate a loan statement and need help with a formula to calculate the dates on which payments/interest are paid/charged.

Payments are paid fortnightly and interest is charged at the end of each calendar month. For example if the loan began on the 1st April, the first payment would be paid on the 15th April, the next payment would be made on the 29th April and interest would be charged on the 30th April and so on.

The table would look something like this:

15-Apr Payment
29-Apr Payment
30-Apr Interest Charge
13-May Payment
27-May Payment
31-May Interest Charge
10-Jun Payment
24-Jun Payment
30-Jun Interest Charge

and so on...

I've been playing around with a formula (I found on one of the older posts) that finds the end of the month given a date reference:

ie. DATE(YEAR(reference),1+MONTH(reference),0)

along with an IF statement but can't seem to recreate the dates I need as above...

Any would be appreciated...

 

[bryhamm]

book3
	A	B	C	D
1	4/15/04	payment
2	4/29/04	payment
3	4/30/04	interestcharge
4	5/13/04	payment
5	5/27/04	payment
6	5/31/04	interestcharge
7	6/10/04	payment
8	6/24/04	payment
9	6/30/04	interestcharge
10	7/8/04	payment
11	7/22/04	payment
12	7/30/04	interestcharge
Sheet1

If I am understanding you, the payment formula would simply add 14 days to the initial payment entry.

I believe it is the interest charge date that you are trying to figure out. I am assuming that this is to be the last business day of the month.

The above formulas will work, but keep in mind there will be 2 times a year where there should be a 3rd payment in a calendar month and you will need to adjust accordingly.

 

[2rrs]

You may find this of some use.
They have a biweekly spreadsheet available

http://www.mtgprofessor.com/spreadsheets.htm

 

[kristina_q]

Thanks bryhamm... yes you're right I'm having trouble with the date the interest is charged... will try your suggestion and see how I go.

2rrs - have stumbled across mtgprofessor's site a few wks ago... found it useful for a few other loan calcs but not as useful in dealing
with my date problem. Thanks, anyway!