2 Replies Latest reply: May 15, 2014 9:53 AM by Wendy _ RSS

    NPV calculation

    Wendy _

      I am trying to use Monarch to do a NPV calculation.

       

      I have a file with 100 periods of cashflow payments with their associated payment dates.  I would like to discount them at a certain rate.

      The file is from a txt format. 

       

      Can someone help me?

       

      Thanks,

      Wendy

        • NPV calculation
          Grant Perkins

          Hi Wendy,

           

          Two questions.

           

          I see from your profile you use Monarch Version 8. Is that the Standard or Pro version? (It may be useful to know that when suggesting a solution for you.)

           

          Secondly are you looking to discount at the same rate for all the periods or at a different rate for each period? Or as some sort of calculation based on a fixed rate and age combination?

           

          If it is the simple flat rate case then a calculated field which takes the value from the report and multiplies it by the rate will provide an answer.

           

          You could also set this up to be more flexible by creating a Runtime Parameter field that prompts for the rate to use for the calculation. Your calculated field would then be the value from the report adjusted by the value in the in the Runtime Parameter field.

           

          If you have a list of adjustment values according to period and/or age I would guess you need to be looking at either an Internal Lookup table or, if you have the Pro version, maybe an external lookup table.

           

          Use an internal table if the values are likely to be fixed and unchanging (or you have only the Standard version.) Use an external table if the values are constantly adjusted (and you have the Pro version.)

           

          You will also need to work out the best way to link the two tables. It would most likely be by 'period' based on my reading of your question.

           

          How complicated it would be to work out the period I don't know but it sounds like it is already done for you on the report.

           

          Does this help at all?

           

          Grant

          • NPV calculation
            Wendy _

            Grant,

            I am using version 8.02 Pro. I am discounting at the same rate for each period. My payment information are based on horizontal layout with accounts lined up vertically. I've used external lookup based on a vertical column and to use it for this example, I would need to repeat the look up 100 times.

            I used your suggestion by using the disc rate in the run time parameter. I then discounted each payment period separately using disc rate from the run time parameter. 

            Thanks,

            Wendy