Interest rate calculation

[footoo]

What is the formula to calculate the annual interest rate from the following :
Start date – 1 Jan 2018
Start value - $100,000
End date – 31 Dec 2028
End value - $160,000
Interest – compounded monthly

 

[steve the fish]

I dont use these financial functions much but this seems to work ok:

=RATE(11, 0, -100000, 160000)

 

[footoo]

Thanks, but that works only for either
- end date 31 Dec 2018
or
- end date 31 Dec 2028 with interest compounded once per year.

How can it be adjusted for : end date 31 Dec 2028 with interest compounded each month?

 

[footoo]

OK, got it :
=RATE(119, 0, -100000, 160000)

Thanks.

 

[steve the fish]

You said you wanted the annual interest rate. The 11 is just the number of periods so if you want monthly you just need to calculate the number of months between your dates. eg 11*12

 

[footoo]

Thanks.