Working out interest lost on a loan

[MJN78]

I'm trying to work out how much interest I have lost by investing an
amount into a project.

So, I'd have to use the following information which would be in my
excel worksheet:

date of loan: 25/4/2006
amount of loan 30000
interest rate potentialy earned at bank: 6%
todays date: 20/6/2006

Excel should then work out how much interest has been lost by not
having the money invested in a bank given the time elapsed between the
date of loan and todays date. To obtain an accurate figure given
compound interest I'd also need to tell it how often interest is paid -
daily, monthy etc. So maybe another cell.

interest interval : 12 (12 times per year for monthly)

I've tried the financial funtions but they don't seem to quite fit - or
maybe I'm not doing it correctly. Could anyone guide me to a formula
that would help?

Thanks,

Mark.

 

[Don]

Mark

I don't know how UK banks pay interest, but US banks generally pay daily
compounded - actual/acutal interest. The interest would be $277.42:

(1+0.06/365)^(enddate-startdate)*30000-30000 = 277.42


Don

 

[MJN78]

Thanks Don,

This does exactly what I wanted.

Mark.

 

[joeu2004]

(1+0.06/365)^(enddate-startdate)*30000-30000 = 277.42

This does exactly what I wanted.

If that is "exactly" what you wanted, since your original posting
indicated a frustration with using Excel financial functions, it might
interest you to know that the following financial function formulation
does exactly the same thing:

=fv(6%/365, date(2006,6,20) - date(2006,4,25), 0, -30000) - 30000

I am not convinced that either formula is "exactly" what you asked for.
But I am confused by the way you expressed the question, so perhaps I
misunderstood.