Forum Discussion
Calculate NPV for non regular date & amount
I have some comments and questions below.
But to answer your question..... Yes, there is one definite error and potentially another error in the NPV calculation.
Refer to the "mod" worksheet in my Excel file attachment.
-----
1. In C5 and E5 in the "orig" worksheet, the NPV values range does not include Mar-22 (zero). It should.
I would make the following changes (see the yellow highlighted cells):
Formulas:
C5: =NPV(B5/12,C9:C114)+C7+C8
E5: =NPV(B5/12,E9:E114)+E7+E8
In effect, I moved the Apr-22 row down, and I inserted a row for Mar-22. Then I renumbered the payments.
I also made the change in the NPV formulas that is highlighed in red above.
Although the syntactic change is C113 to C114 and E113 to E114, the important semantic change is: C9 and E9 now refer to Mar-22 instead of Apr-22.
Also, now the last payments are correctly numbered 96 for the 8-year option and 60 for the 5-year option.
-----
2. It is not clear to me whether the NPV discount rate in C5 and E5 should be B5/12, or it should be changed to (1+B5)^(1/12) - 1.
That depends on how we should interpret the annual rate in B5.
In some countries, the annual rate is a simple rate. In that case, B5/12 is the correct monthly rate.
In other countries, the annual rate is a compounded rate. In that case, (1+B5)^(1/12) - 1 is the correct monthly rate.
(And then there is Canada. Sigh.)
-----
3. The required change #1 above has a consequential effect on the NPV values in C5 and E5. Also, they are no longer the same.
I believe they should be.
And in fact, I believe the deferred payments for both options should be recalculated so that the NPV is 5,315,367(?), as it is in the "orig" worksheet.
(Assuming that 5,315,367 is the correct cost, excluding profit, of the property sold. Is it?)
See my comments below, notably how I believe the equal deferred payments are calculated in #4. If you agree, I can show you how to calculate them.
-----
My comments and questions....
Refer to the "orig" worksheet (yours) in the attached Excel file.
4. I wrote: ``how did you determine the last payment in C103 (200,438)?``
hussein_elsayed wrote: ``calculated, to be equal with the total amount in C2``
Then, C103 would be =C2-SUM(C7:C102).
But that results in 200436.6, which rounds to 200,437, not 200,438.
Instead, I believe the equal deferred payments are calculated so that their NPV (plus down payment and contract payment) is the same as the "cost" of the loan (see #5 below). Thus, the equal deferred payments are 200411.656041646 and 252028.385606053.
Then, the equal deferred payment for column C is rounded down, and the last deferred payment in C103 is calculated so that, again, the NPV of the deferred payments (plus down payment and contract payment) is the same as the "cost" of the loan. Thus, the last deferred payment is indeed 200438, rounded.
-----
5. I wrote: ``How did you determine the value in C2 (6,903,074)?
hussein_elsayed wrote: ``According to the pricing list of units that we have in our company``
I don't know what "pricing list of units" means.
But I wonder if you are talking about financing a purchase ("loan") in accordance with Islamic law, where the agreed-upon sales price (C2) is the sum of the cost ("principal" for a non-Muslim loan) plus profit ("total interest").
You probably know (or should know) the cost. In a non-Muslim loan, that is the actual price of the property sold.
(The cost, exlcuding profit, should be the same as the NPV of the deferred payments plus the down payment and contract payment, which is calculated in C5 and E5.)
The cost will be the same for both deferred payment options (column C and column E). Note that C5 and E5 are the same.
However, the profit will vary depending on the number, schedule and amounts of the deferred payments.
Thus, I believe the "total amount" is correctly calculated in C114 and E114. They are the sum of the deferred payments plus down payment and contract payment. Note that C114 and E114 are different.
I think it is incorrect that the expected total amounts in C2 and E2 are the same. Note that E2 is merely the formula =C2.
-----
6. hussein_elsayed wrote: ``the annual rate are given as per the policy of my company``
I doubt that, or that explanation seems incomplete.
The annual rate in B5 is calculated. I am asking: how is it calculated?
(This is mainly for my education. But it might help me help you resolve the potential issue that I noted in comment #2.)
If it is calculated by a formula, what is it, and what are the values and descriptions of any dependent cells references?
If it is derived using Solver, what is the Solver set-up, and again, what are the dependent values and descriptions?
Show all values with the precision of 15 significant digits.
IMHO, the simplest thing to do is: attach an Excel file that demonstrates how the annual rate is calculated.
I know that the value in B5 is calculated because....
The value in B5 appears to be 7.23276435473547%. I doubt that any company specifies a "finance" (interest or profit) rate with 14 percentage decimal places -- 15 significant digits, coincidentally.
Moreover, the exact value is 7.23276435473547% - 2.78E-17. That is a telltale indication of a binary calculation. It only appears to be 7.23276435473547% because of Excel's arbitrary formatting limitation.
-----
7. The down payment in C7 and E7 (and probably the contract payment in C8 and E8) are calculated by a percentage of the "total amount" in C2 and E2.
That makes it difficult (impossible?) to calculate the amounts of the deferred payments, because the amount of profit in the "total amount" depends on the schedule of deferred payments. Ostensibly, that is a circular calculation.
On the contrary, I suspect that the down payment and contract payment should be a percentage of just the cost of the property, excluding profit. That would be consistent with non-Muslim loans.
As I noted above, you probably know (or should know) the cost of the property, excluding profit.
i'd like to thank you for your greet effort to help me, and sory for being late as i was so tired the past two days.
let me clarify the steps we follow to calculate the NPV
1- the pricing list is approved price for all units including profit (inventory that contains alot of units with prices including the profit)
2- once we have the price of unit C2, and the defualt of payment plan is the culmn C that shows the client have to pay 5% down payment , contract payment is 0 and delevery payment C26 is 5% (the payment should be paid before the handingover of the unit) and the rest amount devided equaly for 8 years
3- top managment agreed that the cash discount should be 77% from the total unit price (c2*77%)
then by what if analysis i got the discount rate B5
4- the customer have an option to change the default payment plan as i mentioned earlier to get the proper discount, then he will pay large amount as an advance payment and the delivery payment is the same as the original then by what if analysis we equal the two NPV in C5 & B5 and get the new payments according to that.
i hope i clarify more than earlier and thank you in advance
- I apologize for the many questions that i asked, knowing that I have got benefit a lot from your information and experience
Thank you so much. hussein_elsayed wrote: ``I hope that your patience will last until the matter is completed``
I am afraid not. I can answer simple usage questions. But it seems that you are looking for a complete application that supports a broad range of alternatives. I do not have time for that.
If this is not an academic assignment and you are (your company is) truly in the business of selling properties that are worth many millions of "dollars", you (your company) can afford to hire a professional who can work with you to implement an application that meets your requirements.In fact, I think you should ignore everything I have written so far.
I worry that it is what we call "garbage in, garbage out". I have never fully understood the financial arrangement that you are talking about. So, I cannot be sure that I am solving your problem correctly.
I will try to answer your last questions. But I will not be able help you further. Sorry.
-----
Re: "IRR PV" in C12 and E12....
Yes, that is the "present value" of the cash flows starting on Mar-22 in rows 18 through 123. "IRR PV" should be read as "the present value (PV) for the IRR calculation".
For the purpose of using the Excel IRR and XIRR functions, cash flows must be signed (plus or minus) in order to distinguish inflows from outflows.
Since your deferred payments in rows 18:123 are positive, the "present value" should be negative, at least for the purpose of using those functions.
However, my "IRR PV" calculation in C12 and E12 assumes that all cash flows other than the deferred payments in rows 18:123 all occur in Feb-22, which I assume is the "present time" (that is, the date associated with the "present value").That applies to the discounted cost (5,315,367 in C3), the down payment (345,154 in C5 and C17), and the contract payment (zero in C7 and C18).
I explained that assumption before. But I do not believe you ever confirmed or corrected it.
-----
Re: Excel NPV and Excel IRR versus XNPV and XIRR....
They do produce different results. But neither is "more accurate" than the other.
Some people believe XNPV and XIRR are more "precise" because they take into account the fact that not all months have the same number of days. Instead, they have 28, 29, 30 and 31 days.
In contrast, Excel NPV and Excel IRR treat each month as if they have the same number of days.
In my opinion, sometimes that degree of "precision" of XNPV and XIRR is misleading. It really depends on how __you__ want to treat each month.
For example, the same sequence of cash flows might have different results depending on which month and year corresponds to the first cash flow. Do you really want that degree of "accuracy"?
It is important to understand that XIRR returns an annual rate that is compounded daily; and XNPV expects the same.
In contrast, your use of Excel IRR returns a monthly rate; and your use of Excel NPV expects the same.
We can convert the monthly rate to a "simple" annual rate by multiplying by 12, as you do. Or we can convert it to an annual rate that is compounded monthly.
Also, we can convert the Excel IRR monthly rate to an annual rate that is compounded daily. But that will not agree with the XIRR rate, because of the different assumptions about the number of days per month.
It is also important to be consistent in your use of the results.
As a counter-example, some people use XIRR to calculate an annual rate, then divide that by 12 to calculate a monthly rate for use with other financial functions. __That__ does produce "inaccurate" results.
-----
Re: A "line-by-line" monthly cash flow model....
You can use either XNPV (and XIRR) or Excel NPV (and Excel IRR) with a cash flow model that has a line for each month, with some monthly cash flows being zero.
One nice thing about using XNPV and XIRR is: you can have multiple lines with the same date. In effect, it is as if the individual cash flows are combined into one "net" cash flow on that date.
In contrast, if you have multiple lines with cash flows that you intend to be treated as if they occur on the same date, your use of Excel NPV and Excel IRR will treat them instead as separate monthly cash flows. We must combine them into one "net" cash flow either manually or with a formula, as I did with "IRR PV" in C16.
-----
Okay, I believe that is all the help that I can offer.
If you have specific usage questions, I might be able to address them.
But if you have questions about applying that to your financial arrangement (deferred payment plan), I should not try to help you because I do not fully understand the financial arrangement.
You should be sure that anyone who does try to help you with that does fully understand your financial arrangement.
@hussein_elsayed wrote: ``I have a questions regarding the new mod``
I will try to answer your questions. But I will not be able to it now because it is late at night, my time. Also, I think I need more information from you.
And in order for this to work, you must do what I asked: refer to amounts by their value or cell reference, not just by what you call them.
For example, I do not know what you mean by the term "reservation payment". But if you write "the reservation payment 5,315,367" or "the reservation payment in C3", I will know what you mean.
If you do not do what I ask, I cannot help you further because I cannot understand what you are asking.
- sorry for missleading words. am just want to say the down payment in cells C16 & E16
-----
@hussein_elsayed wrote: ``if i want to add some criteria to the mod what i can do (the client want to pay the rest of amount after 3 month from reservation payment or he want to pay semi annual or annual - the customer want to pay over 10 years- the client want to pay quarterly)``
Yes, we can modify the formulas to accommodate that flexibility.
But before I invest a lot of my time to implement a solution, you need to decide:
1. Do you want to use the "NPV model" that you have in your "orig" worksheet, with one row for each month, even if the payment might be zero?
2. Or do you want to use the "XNPV mode" that you have in your original posting, with just the rows with non-zero payment amounts and the corresponding date.
I asked you that question previously, and you said that you want to use the "NPV model" (#1).
But now you are asking questions about the "XNPV model" (#2).
I do not have time to provide solutions both ways.
So please choose one method and stick with it.
- i want the model that will providing me with the accurat discount so from your experience what is the model that will do this. i will depend on what you will suggest to me to work with (taking into considration that the client want to see his payments during the perioud (line by line) also knowing his discount if he changes his payment plan
-----
@hussein_elsayed wrote: `` if i want to use xnpv method is it will drive to an accuate figures if yes, could you please assist regarding that``
Yes. But I will do things only one way or the other, not both.
So again, choose which way that you want to proceed.
What is your answer?
if the xnpv will drive to accurate discount and appears line by line like the NPV (as i mentioned in the above) and according to your above feedback from your experience. i will depend on the xnpv Model
-----
@hussein_elsayed wrote: ``- reference to the irr in the mod sheet, is it the present value [....] If no what is the dufferent between them``
I do not understand the question because you do not refer to amounts or cell references in the "mod" worksheet.
In the "mod" worksheet, I use the term "IRR PV" in A12, and I calculate the amount in C12 and E12.
I also have the label "Discount rate (IRR)" in A13, and I calculate the percentage rate in C13 and E13.
Which one are you asking about? Or are you asking about something else?
- cells C12 & E12
Actually, __you__ used the term "discount rate" in B6 in the "orig" worksheet. With my label in A13, I am merely pointing out that that is the same as the "IRR".
-----
@hussein_elsayed wrote: ``reference to the mod sheet what do you men when you put feb 2022 for reservation payment and the same month for contract payment, is that meen somthing as actualy we have 3 month between the contract payment and the reservation payment``
I probably do not understand the question. In particular, I do not understand what you mean by "reservation payment". And I do not understand what you mean by "3 months between" the contract payment and the reservation payment.
One thing for you to understand: you and I use very different terminology. I do not know how much of that difference is due to translation between languages, and how much of that difference might be due to differences between the structure of the "loan" (Islamic-conforming versus non-Muslim?).
So, I make a lot of guesses. I take some "cues" from the formulas that you provide in the "orig" worksheet. Here is what I assume....
On 1 Feb 2022 ("Feb-22"), you initiated a "deferred payment plan" ("loan") with a "principal" amount of 4,970,213 that is calculated as follows:
5,315,367 cost (C3)
- 345,154 down payment (C16)
- 0 contract payment (C17)
----------
4,970,213 principal
The principal plus profit ("interest") is repaid by a series of monthly deferred payments, some of which are zero. The monthly deferred payments begin (as early as) Mar-22.
That timeline seems to be consistent with the calculations that you have in column C of the "orig" worksheet, with the "corrections" that I have in the "mod" worksheet.
If I misunderstand the timeline, please tell me the correct timelime. It might change other calculations.
But remember to refer to elements of the timeline (plan initiated, down payment paid, contract payment paid, deferred payments paid) by referring to cell and values in the "mod" worksheet.
Also, show how you calculate any amount that is not already calculated in a cell in the "mod" worksheet, just as I did above.
Remember: if you do not follow my instructions, I cannot help you further because I probably cannot understand.
I benefited a lot from your help in the previous messages, and I hope that your patience will last until the matter is completed. Thank you very much
hussein_elsayed wrote: ``I have a questions regarding the new mod``
I will try to answer your questions. But I will not be able to it now because it is late at night, my time. Also, I think I need more information from you.
And in order for this to work, you must do what I asked: refer to amounts by their value or cell reference, not just by what you call them.
For example, I do not know what you mean by the term "reservation payment". But if you write "the reservation payment 5,315,367" or "the reservation payment in C3", I will know what you mean.
If you do not do what I ask, I cannot help you further because I cannot understand what you are asking.
-----
hussein_elsayed wrote: ``if i want to add some criteria to the mod what i can do (the client want to pay the rest of amount after 3 month from reservation payment or he want to pay semi annual or annual - the customer want to pay over 10 years- the client want to pay quarterly)``
Yes, we can modify the formulas to accommodate that flexibility.
But before I invest a lot of my time to implement a solution, you need to decide:
1. Do you want to use the "NPV model" that you have in your "orig" worksheet, with one row for each month, even if the payment might be zero?
2. Or do you want to use the "XNPV mode" that you have in your original posting, with just the rows with non-zero payment amounts and the corresponding date.
I asked you that question previously, and you said that you want to use the "NPV model" (#1).
But now you are asking questions about the "XNPV model" (#2).
I do not have time to provide solutions both ways.
So please choose one method and stick with it.
-----
hussein_elsayed wrote: `` if i want to use xnpv method is it will drive to an accuate figures if yes, could you please assist regarding that``
Yes. But I will do things only one way or the other, not both.
So again, choose which way that you want to proceed.
What is your answer?
-----
hussein_elsayed wrote: ``- reference to the irr in the mod sheet, is it the present value [....] If no what is the dufferent between them``
I do not understand the question because you do not refer to amounts or cell references in the "mod" worksheet.
In the "mod" worksheet, I use the term "IRR PV" in A12, and I calculate the amount in C12 and E12.
I also have the label "Discount rate (IRR)" in A13, and I calculate the percentage rate in C13 and E13.
Which one are you asking about? Or are you asking about something else?
Actually, __you__ used the term "discount rate" in B6 in the "orig" worksheet. With my label in A13, I am merely pointing out that that is the same as the "IRR".
-----
hussein_elsayed wrote: ``reference to the mod sheet what do you men when you put feb 2022 for reservation payment and the same month for contract payment, is that meen somthing as actualy we have 3 month between the contract payment and the reservation payment``
I probably do not understand the question. In particular, I do not understand what you mean by "reservation payment". And I do not understand what you mean by "3 months between" the contract payment and the reservation payment.
One thing for you to understand: you and I use very different terminology. I do not know how much of that difference is due to translation between languages, and how much of that difference might be due to differences between the structure of the "loan" (Islamic-conforming versus non-Muslim?).
So, I make a lot of guesses. I take some "cues" from the formulas that you provide in the "orig" worksheet. Here is what I assume....
On 1 Feb 2022 ("Feb-22"), you initiated a "deferred payment plan" ("loan") with a "principal" amount of 4,970,213 that is calculated as follows:
5,315,367 cost (C3)
- 345,154 down payment (C16)
- 0 contract payment (C17)
----------
4,970,213 principal
The principal plus profit ("interest") is repaid by a series of monthly deferred payments, some of which are zero. The monthly deferred payments begin (as early as) Mar-22.
That timeline seems to be consistent with the calculations that you have in column C of the "orig" worksheet, with the "corrections" that I have in the "mod" worksheet.
If I misunderstand the timeline, please tell me the correct timelime. It might change other calculations.
But remember to refer to elements of the timeline (plan initiated, down payment paid, contract payment paid, deferred payments paid) by referring to cell and values in the "mod" worksheet.
Also, show how you calculate any amount that is not already calculated in a cell in the "mod" worksheet, just as I did above.
Remember: if you do not follow my instructions, I cannot help you further because I probably cannot understand.
You have done agreet favor for me as i have learned alot of my mistakes, and I have a questions regarding the new mod you've been sent which is
- if i want to add some criteria to the mod what i can do (the client want to pay the rest of amount after 3 month from reservation payment or he want to pay semi annual or annual - the customer want to pay over 10 years- the client want to pay quarterly)
- if i want to use xnpv method is it will drive to an accuate figures if yes, could you please assist regarding that
- reference to the irr in the mod sheet, is it the present value correct me if i am wrong. If no what is the dufferent between them
- reference to the mod sheet what do you men when you put feb 2022 for reservation payment and the same month for contract payment, is that meen somthing as actualy we have 3 month between the contract payment and the reservation payment ( my company policey in som projects).
Thank you for your kind assisthussein_elsayed wrote: ``i hope i clarify more than earlier``
Yes.
-----
hussein_elsayed wrote: ``by what if analysis i got the discount rate B5``
But you made the same mistake that I noted regarding your NPV calculation: you omitted the Mar-22 payment (zero).
So, for the default payment plan, the correct "discount rate" is approximately 7.07162190317785%, not 7.23276435473547%.
(EDIT.... Or perhaps I should say that is a side-effect of your mistake with NPV.)
-----
hussein_elsayed wrote: ``by what if analysis we equal the two NPV in C5 & B5 and get the new payments according to that``
As I conjectured in one of my earlier responses.
So, for the alternative payment plan, the correct equal deferred payment is 252,555, not 252028.385606771. The last deferred payment is 252,560.
Also note that for the default payment plan, the last deferred payment is 200,436, not 200,438.
The following shows the correct calculations. They avoid the need for "what if" analysis. See the "mod" worksheet in the attached file.
I expect you to study the formulas. If you have any further comments or questions, I expect you to refer to those formulas, specifying the cell references.
Key formulas (there are others):
C3: =ROUND(C1*C2, 0)
C10: =INT((C1-C5-C7-C8)/C9)
C11: =C1-C5-C7-C8 - (C9-1)*C10
C12: =-(C3-C5-C7)
C13: =12*IRR( (C12, C18:C123) )
C14: =NPV(C13/12, C18:C123) + C5+C7
E10: =INT( (E3-E5-E7 - E8/(1 + $C$13/12)^A36)
/ SUMPRODUCT( F18:F123 / (1 + $C$13/12)^A18:A123) )
E11: =ROUND( ( E3-E5-E7 - E8/(1 + $C$13/12)^A36
- SUMPRODUCT(E10*F18:F76 / (1 + $C$13/12)^A18:A76) )
* (1 + $C$13/12)^A77, 0 )
The key point is: the "discounted cost" in C3 __is__ the NPV of the deferred payments plus down payment and contract payment.
So, the NPV calculation in C14 and D14 is unnecessary, except as a check of the other calculations.
Another key point is: the IRR and NPV calculations must include the Mar-22 payment (zero).
The calculations in E10 and E11 are based on the mathematical NPV calculation. See the Excel NPV help page.
The difference in the "discount rates" (IRRs) for the two plans in C13 and E14 is due to rounding the deferred payments to integers.
-----
PS.... I should have formatted the calculated NPV in C14 and E14 to display 8 decimal places (15 significant digits) to emphasize that the calculated "discount rate" in C13 is an approximation. And so is the "discount rate" that you derived with "what if" analysis.
Also note that certain cell references are "hardcoded" (highlighed in red) in the E10 and E11 formulas. Thus, they must be changed manually if you change the payment# for the delivery payment (A36) and the payment# for the last payment (A77). The "hardcoding" might be avoidable, by using the flags in column F a little differently, for example. I did not want to over-complicate things.