Dears,How to calculate the NPV for the modified table if we pay 1,500,000 during feb 22 amd pay 345,154 during sep 23 and pay the reset equaly over the 8 years , Cash discount 8% PMT 1345,154Feb-22PMT 2200,411Aug-22PMT 3200,411Nov-22PMT 4200,411Feb-23PMT 5200,411May-23PMT 6200,411Aug-23PMT 7345,154Sep-23PMT 8200,411Nov-23PMT 9200,411Feb-24PMT 10200,411May-24PMT 11200,411Aug-24PMT 12200,411Nov-24PMT 13200,411Feb-25PMT 14200,411May-25PMT 15200,411Aug-25PMT 16200,411Nov-25PMT 17200,411Feb-26PMT 18200,411May-26PMT 19200,411Aug-26PMT 20200,411Nov-26PMT 21200,411Feb-27PMT 22200,411May-27PMT 23200,411Aug-27PMT 24200,411Nov-27PMT 25200,411Feb-28PMT 26200,411May-28PMT 27200,411Aug-28PMT 28200,411Nov-28PMT 29200,411Feb-29PMT 30200,411May-29PMT 31200,411Aug-29PMT 32200,411Nov-29PMT 33200,438Feb-30 6,903,075

hussein_elsayed wrote: ``How to calculate the NPV`` See the XNPV help page. If your values are in B2:B34 and your dates are in C2:C34: =XNPV(8%,B2:B34,$C$2:$C$34) -----hussein_elsayed wrote: ``for the modified table if we pay 1,500,000 during feb 22 amd pay 345,154 during sep 23 and pay the reset equaly`` I am not sure I understand. That does not match the data that you posted. So, my interpretation is: you want to compare the NPV of the posted cash flows with the NPV of the cash flows with the modifications that you describe. See the attached Excel file. Formulas:B35: =SUM(B2:B34)B36: =XNPV(8%, B2:B34, $C$2:$C$34)D3: =ROUND((B35 - D2 - D8) / (COUNT(B3:B34) - 1), 0)D4:D33 (except D8): =$D$3D34: =B35 - SUM(D2:D33)D35: =SUM(D2:D34)D36: =XNPV(8%, D2:D34, $C$2:$C$34)

This is a deferred sale and the normal one (the sent figures) , and for the numbers sent, it is the normal situation if the payment is made in the normal way. But if the customer pays an advance payment of 1,500,000 and the rest is as explained previously, what is the appropriate discount that the customer will get if we take into account that he paid an amount greater than what is supposed to be paid Also, if he pays irregular amounts in irregular months, bearing in mind that the discount rate is 8%.I hope I have made it clear, and thank you in advance

hussein_elsayed wrote: ``I hope I have made it clear`` I'm afraid not. My only "understanding" of the concept of "deferred sale" is in the context of a (US) deferred sale trust (click here), which I read about only just now(!). Read: I don't really know much about it. And your description sounds like something very different altogether. I'm not sure I understand the context that you describe. In hindsight, my "TMI" addendum might have added to the confusion. So, I will delete it. I believe the calculations that I provided correctly demonstrate the difference in the NPV of the two payment streams, for what it's worth.

JoeUser2004 in the real estate company we sold units with installments over many years (8 YRS), we offer an option to the customers to change the default senario and pay large amount in advance to get an discount on the total price as shown in the attached file, so i need to make sure that i calculate it in accurate way as it has afinancial impact

hussein_elsayed wrote: ``we offer an option to the customers to change the default senario and pay large amount in advance to get an discount on the total price`` I think you want to know how to calculate the equal, but irregularly-paid payment (E6) for the second option (E8:E115) that results in the same NPV (C5, E5) as the first option (C8:C115). And I think you want to calculate the amount of or percentage difference (E117) between the sum of the cash flows (C116, E116) for the first and second options. But before I fully explain details and the choices you might make, I need to know: is that right? If not, forgive me for the misdirection. [EDIT.... In hindsight, it was. Mea culpa! I've deleted the misdirected details.] If I am right, please explain: 1. Do you want to use a monthly cash flow model, as you do in your example Excel file? Or do you want to use the more consise cash flow model that you presented in your original posting? 2. How did you determine the annual interest rate (B5)? It is different from the NPV discount rate (8%) in your original posting? 3. For the original payment schedule (C8:C115), how did you determine the last payment in C105 (200,438), which differs from the other equal payments (200,411) starting in C15? 4. How did you determine the value in C2 (6,903,074)? And what purpose does it serve, if any? It is close to, but not equal to the sum of the cash flows (C116) for the first option.

Calculate NPV for non regular date & amount

15 Replies

JoeUser2004
Bronze Contributor
Mar 01, 2022
hussein_elsayed wrote: ``How to calculate the NPV``

See the XNPV help page. If your values are in B2:B34 and your dates are in C2:C34:

=XNPV(8%,B2:B34,$C$2:$C$34)

-----
hussein_elsayed wrote: ``for the modified table if we pay 1,500,000 during feb 22 amd pay 345,154 during sep 23 and pay the reset equaly``

I am not sure I understand. That does not match the data that you posted. So, my interpretation is: you want to compare the NPV of the posted cash flows with the NPV of the cash flows with the modifications that you describe.

See the attached Excel file.

Formulas:
B35: =SUM(B2:B34)
B36: =XNPV(8%, B2:B34, $C$2:$C$34)
D3: =ROUND((B35 - D2 - D8) / (COUNT(B3:B34) - 1), 0)
D4:D33 (except D8): =$D$3
D34: =B35 - SUM(D2:D33)
D35: =SUM(D2:D34)
D36: =XNPV(8%, D2:D34, $C$2:$C$34)

npv irreg cf irreg dates 2.xlsx12 KB
- hussein_elsayed
  Brass Contributor
  Mar 02, 2022
  This is a deferred sale and the normal one (the sent figures) , and for the numbers sent, it is the normal situation if the payment is made in the normal way. But if the customer pays an advance payment of 1,500,000 and the rest is as explained previously, what is the appropriate discount that the customer will get if we take into account that he paid an amount greater than what is supposed to be paid Also, if he pays irregular amounts in irregular months, bearing in mind that the discount rate is 8%.
  I hope I have made it clear, and thank you in advance
  - JoeUser2004
    Bronze Contributor
    Mar 02, 2022
    hussein_elsayed wrote: ``I hope I have made it clear``
    
    I'm afraid not. My only "understanding" of the concept of "deferred sale" is in the context of a (US) deferred sale trust (click here), which I read about only just now(!).
    
    Read: I don't really know much about it. And your description sounds like something very different altogether. I'm not sure I understand the context that you describe.
    
    In hindsight, my "TMI" addendum might have added to the confusion. So, I will delete it.
    
    I believe the calculations that I provided correctly demonstrate the difference in the NPV of the two payment streams, for what it's worth.

Forum Discussion

Calculate NPV for non regular date & amount

15 Replies

Resources

PMT 1	345,154	Feb-22
PMT 2	200,411	Aug-22
PMT 3	200,411	Nov-22
PMT 4	200,411	Feb-23
PMT 5	200,411	May-23
PMT 6	200,411	Aug-23
PMT 7	345,154	Sep-23
PMT 8	200,411	Nov-23
PMT 9	200,411	Feb-24
PMT 10	200,411	May-24
PMT 11	200,411	Aug-24
PMT 12	200,411	Nov-24
PMT 13	200,411	Feb-25
PMT 14	200,411	May-25
PMT 15	200,411	Aug-25
PMT 16	200,411	Nov-25
PMT 17	200,411	Feb-26
PMT 18	200,411	May-26
PMT 19	200,411	Aug-26
PMT 20	200,411	Nov-26
PMT 21	200,411	Feb-27
PMT 22	200,411	May-27
PMT 23	200,411	Aug-27
PMT 24	200,411	Nov-27
PMT 25	200,411	Feb-28
PMT 26	200,411	May-28
PMT 27	200,411	Aug-28
PMT 28	200,411	Nov-28
PMT 29	200,411	Feb-29
PMT 30	200,411	May-29
PMT 31	200,411	Aug-29
PMT 32	200,411	Nov-29
PMT 33	200,438	Feb-30
	6,903,075