0
$\begingroup$

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

$\endgroup$
  • $\begingroup$ If you accumulate the monthly cash flows into annual totals and then discount them, that's conceptually different from discounting the monthly cash flows directly. I am not sure I undertsand why you are trying to make these two different things come out the same. $\endgroup$ – noob2 Feb 17 at 18:56
  • $\begingroup$ Noob2. I'm glad you highlighted that conceptual point. My thinking was: If I can make them come out the same, then I would be able to find the effective discount monthly rate. Are you saying that I can't find the effective monthly discount rate via this approach? If not, what approach would you suggest? $\endgroup$ – questioner56 Feb 17 at 19:12
  • $\begingroup$ The problem I think is that there is not a unique rate that will make the PV of the monthly cash flows equal to the yearly in all cases. It depends on the timing of cash flows within the year. By going to annual figures you are neglecting a certain amount of detail and that PV is never going to be exactly the same as the more detailed monthly calculation shows. (I am stil thinking about it, I hope this makes sense). $\endgroup$ – noob2 Feb 17 at 19:18
  • $\begingroup$ @noob2 Re: "as the more detailed monthly calculation shows" yes that's what I seemed to find. Re: "It depends on the timing of cash flows within the year" if the timings are consistent, ie. monthly, would we be able to get a unique rate? $\endgroup$ – questioner56 Feb 17 at 19:21
  • $\begingroup$ I could summarize a 1 year project as an annual cash flow of 400, and we could discount this at 10% to get $PV_A$. The quarterly cash flows could be (100,100,100,100) or maybe (98,99,101,102) or perhaps (102,101,99,98). How can we find a single quarterly discount rate that would make these 3 PVs equal to each other and also equal to $PV_A$. I do not think we can. $\endgroup$ – noob2 Feb 17 at 19:27
0
$\begingroup$

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 $$

| improve this answer | |
$\endgroup$
  • $\begingroup$ Thank you David for your answer. Sorry let me try to clarify: I'm trying to match the sum of discounted cash flows to $1,090.90 not $1,200. This is because, $1,090.90 is the sum of 12 undiscounted monthly cashflows that are then discounted by 10% for a project period (in this case) year number one, ie. 1200/(1+10)^1. Would I be correct in presuming these can be matched? If not, why not? $\endgroup$ – questioner56 Feb 17 at 19:00
  • $\begingroup$ After searching online, I've found two formulae: Monthly rate = [(1 + annual rate)(1/12) – 1]*12 and Monthly rate = (1 + annual rate)(1/12) – 1. You're recommending I use the former formula to calculate the effective monthly discount rate, correct? $\endgroup$ – questioner56 Feb 17 at 19:05
  • $\begingroup$ I've updated the Excel sheet to show the formula and rate you wrote above. I've compared it with other forumulas and rates. As mentioned in my question's comments' discussion with Noob2, would you agree that CF/(1+r/12)^n, where r=0.10 and n=monthly project period would be the best way (and most practical approach) to get the closest answer? The closest answer for all years (y1 is $1090.90, y2 is $991.74 and y3 is $ 901.58). $\endgroup$ – questioner56 Feb 17 at 21:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.