Find out the effective monthly discount rate for a 10% annual discount rate

Question

First time posting. Apologies in advance if this is not the right question for this forum. If it is, please let me know if I should reformat this in a particular way. If it isn't, would it be more suitable for:

pm.stackexchange.com

math.stackexchange.com

or another stackexchange?

My editable Excel sheet can be viewed here (60% zoom may be good for you): https://docs.google.com/spreadsheets/d/14BrOx7CIGpTTq7lkPAJbXbDrGKTlAHYC-Kw6zVDv9o4/edit?usp=sharing

I have monthly cash flows and I am modelling this project for 36 months. For the timebeing, I have 12 project period columns in my Excel sheet and also a column for project period zero (for the initial outlay aka initial investment in the project).

I tried pasting the Excel sheet directly here but it didn't format in a neat way.

I'm trying to find out the effective monthly discount rate (given the project has an annual discount rate of 10%) and the correct formula to use it. I know doing this would be incorrect:

 =Monthly Cash flow/(1+0.10)^month number

I've tried dividing the discount rate by 12, the project period by 12 and both:

 =Monthly Cash flow/(1+0.10/12)^(month number)
 =Monthly Cash flow/(1+0.10)^(month number/12)
 =Monthly Cash flow/(1+0.10/12)^(month number/12)

However, I don't get the exact amount equal for when I discount it annually, ie:

None of the above when summed up for 12 months equal: =1 year's worth of Monthly Cash flow/(1+0.10)^(project year number)

Perhaps this (that it can equal to) is a wrong assumption in the first place. So far i've looked on various sites and still unsure. I've looked up:

http://people.stern.nyu.edu/adamodar/New_Home_Page/littlebook/pvmechanics.htm

https://www.vertex42.com/ExcelArticles/discount-factors.html

https://www.experiglot.com/2006/06/07/how-to-convert-from-an-annual-rate-to-an-effective-periodic-rate-javascript-calculator/

Update: Based on @david duarte's answer and my online research (links above) I've come to summarise that there are two formulae that I'm confused about:

 i) Monthly rate = [(1 + annual rate)(1/12) – 1]*12
 ii) Monthly rate = (1 + annual rate)(1/12) – 1

@noob2 seems to be saying that my approach to match an annually discounted cash flow with a sum (of 12) monthly discounted cash flows is conceptually inconsistent.

UPDATE 2: As discussed with @noob2, it's not possible to get an exact match with a formula that has a unique/fixed rate. However, the formula Σ [DCF/(1+r/12)^n], where Σ= summed for 12 monthly projections, DCF=1 months' discounted cash fows, r=the annual discount rate and n=monthly project period (months), (ie. dividing the annual discount rate by 12), appears to be the best (most practical) formula to use. By best I mean it gives the closest answer — closest answer to the annual formula (Σ CF)/(1+r)^n, where Σ= summed for 12 monthly projections, CF=12 months' undiscounted cash flow, r=annual discount rate and n=annual project period (years). This can be observed from the Excel sheet linked above.

I'm leaving this question open in case someone has further explanation or a better approach.

Answer 1

I think that what you want is to convert an annually compounded interest rate to a monthly compounded interest rate, right?

$$\left(1+\frac{r_{monthly}}{12}\right)^{12} = (1 + r_{annual})$$

$$r_{monthly} = 0.09568969$$

Notice that the monthly compounded rate would have to be lower than the annual rate of 10% because you are compounding the proceeds each month but the final result has to be the same.

In your spreadsheet, you seem to be trying to match the sum of the discounted cashflows to $1,200 which is simply the sum of the non discounted cashflows, so I don't see how that would ever work however you express your interest rate.

If you take the discounted CF (1,090.90) and compound it with the monthly compounded rate, you will get $1,200:

$$$1,090.90 \times (1 + 0.09568969 / 12 )^{12} = $1,200 $$

or, doing the inverse, if you discount the $1,200 with the monthly compounded rate you will get the discounted CF:

$$$1,200.00 / (1 + 0.09568969 / 12 )^{12} = $1,090.90 $$

Answer 2

I didn't read in detail above but I don't think I actually saw the correct answer above. If you have an annual rate but monthly cashflows then your discount factor is 1 divided by (1+annual rate)^(1/12) or put another way (1+annual rate)^-(1/12).

You've diving the rate itself by 12 in many versions above and that's part of the problem. Simple way to check if what I told you makes sense.

If you put $1 in the bank for a year at a 10% rate at the end of the year you now have $1.10...assuming the bank waits until the end to pay you.

Now assume the bank pays you on a compounded basis monthly. At the end of month each month you are paid (1+annual rate)^(1/12)...do that 12 times and the sum of your exponents become 1....or you can do it the long way and see what u get each month...once again at year end u now have $1.10.

So the monthly accretion and annual accretion now match.