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?
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.
Bookmarks