Forum Discussion

hussein_elsayed's avatar
hussein_elsayed
Brass Contributor
Mar 01, 2022

Calculate NPV for non regular date & amount

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 1 345,...
excel
JoeUser2004's avatar
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)

 

hussein_elsayed's avatar
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's avatar
    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.

    • hussein_elsayed's avatar
      hussein_elsayed
      Brass Contributor
      Mar 02, 2022

      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

      • JoeUser2004's avatar
        JoeUser2004
        Bronze Contributor
        Mar 03, 2022

        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.

Resources