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:
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:
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.