Interest Calculation

[abmyers]

Hi everyone,

Thanks in advance for your help.

I am trying to write a model to show interest over a period, for a money market fund. The only inputs I want to have are settle date, redemption date, principal investment and interest rate.

Interest is calculated as follows:

Principal * rate * (days/365) = interest

Here is the problem,

Interest is paid on the first of the month, for the days invested the previous month. For instance, on Feb 1, interest is paid for the number of days invested in Jan. The interest is accrued off the reinvested principal + interest for the following month.

Assumptions: Balance never changes, except interest payments (no withdrawls or contributions). Interest rate never changes

I made a sheet, but it is big, and requires a lot of manual data, as the days/month varies each month. How can I make this better?

[oldchippy]

Take a look at this link, it may help?

http://answers.yahoo.com/question/in...0091313AA20Thf

[abmyers]

Hmn. Maybe I was a little too vague.

I know how to calculate the interest, the issue I am having is writing a worksheet that does it more quickly.

Basically, I need a way to put in two dates, say Jan 23, 2007, and May 24, 2007, and have excel automatically recognize that it needs to compound the interest on Feb 1, March 1, April 1, May 1st. I have a worksheet for it right now, but it is long and requires a lot of manual work, with changing the number of lines, etc. If someone would like I can send it to them and they can tell me what to do.

Some more specific questions:

1.) Is there a way to set compounding period to the first of the month?
2.) With monthly compounding, maintain actual/365 day count?
3.) With just a settle date and end date (maturity/redemption date), can excel automatically know how many times to compound and on what dates?