Forum Discussion
Calculate NPV for non regular date & amount
hussein_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.
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 assist
- 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.