Monthly Interest Rate if Yearly rate is known

thunderfoot

Board Regular
Joined
May 28, 2004
Messages
229
If I know the yearly interest Rate (5%) and I want to calculate the Monthly Interest Rate ..... what's the formulae?

I know my answer will be about 0.408% (D5) as I've used 'Goal Seek' in formulae =(1+D5)^12, where the result = 105%.

I did once work-out a formulae based on the formulae shown on the keypad of a scientific calculator but I no longer have the formulae nor the calculator!!!!
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

cmart02

Board Regular
Joined
Feb 14, 2005
Messages
59
It depends on what that 5% stands for. Is it nominal or effective rate?

In you example, you seem to want the nominal monthly equivalent. In this case, you can find it out by:

=NOMINAL(5%,12)/12

So that the result of the above formula can be equated to 5% as: =(1+NOMINAL(5%,12)/12)^12-1

Also, if this is not the case, then what you're looking for is the monthly effective rate:

=EFFECT(F5/12,1)

Thus in the first case you get 0.4074% (nominal monthly equivalent, which is what you got originally) and 0.4167% (effective monthly equivalent.
 

sweater_vests_rock

Well-known Member
Joined
Oct 8, 2004
Messages
1,657
thunderfoot.

*let m = monthly interest rate, r = yearly interest rate.*

if we assume interest compounds monthly, then we would use the formula

(1+m)^12 = (1+r)

solving this equation for m yields

m = (1+r)^(1/12) - 1.

if the interest compounds yearly, then the answer is simply

m = r/12.

hope that helps. ben.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,622
Members
414,082
Latest member
sasmita

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top