Results 1 to 2 of 2

Thread: Present Value Calculation Dependent on a Drop Down option.

  1. #1

    Present Value Calculation Dependent on a Drop Down option.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I'm working on a bond valuation calculator and I want my present value to depend on a compounding method chosen from a drop down list (i.e. annual, semi annual, quarterly, monthly). When a different compounding method is chosen, my rate needs to change accordingly (semi-annual would be rate/2, quarterly would be rate/4 etc.) but I do not want the value to change in the Rate cell, only be reflected in the PV calculation. IS this a matter of nesting IF functions within the rate in my PV function? Or should I associate each option in the dropdown with a number value?

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Using the PV() function, try something like:

    =PV(Rate/Lookup(A2,{"annual","monthly","quarterly","semi-annually"},{1,12,4,2}),nper,pmt,[fv],[type])

    where A2 contains your dropdown menu that contain the strings shown in the formula. Note that the list of string should be listed in alphabetically ascending order, and the corresponding numbers in the second array should correspond to the strings respectively.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •