Working out an interest rate

I've just started trading stock via a loan facility...this has presented an interesting problem for tracking profit and loss. I may purchase the stock and day/weeks later the rate will change... then it will change again.

So I need a way of looking up a series and calcuating an effective interest rate. I figure it will end up with a two colums one with date (of rate change) and the other with the rate.

I've then got another worksheet with ...

date of purchase, quantity, purchase price, current value, interest paid.

So can anyone think of a way to lookup from the date purchased to today() and work out the effective interest rate taking in to account the number of days in each period of change?

I've attached what I think I'm looking for (note Australia date format dd/mm/yy)

Thanks heaps...

(Long time lurker first time poster)

bump .... anyone?

Thanks for the code ... it is something I've tried. That will lookup the current date and apply the rate at that point in time.

I need it to work out the avg rate between two date ranges with a variable interest rate.

Thanks.

Trinity ... Help?

Anyone got any other ideas?

I was thinking today it might be an option through some kind of recursve function. But I don't have the skills to write it.

get the date of stock purchase and store it in a VAR
lookup the interest rate at that date
work out the interest paid until next rate change (or stock sold date)
change the VAR to be the date the rate changed and repeat?

Thoughts?