Present Value Calculation Dependent on a Drop Down option.

themightycod

New member
Joined
Mar 16, 2014
Messages
1
Reaction score
0
Points
0
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?
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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.
 
Top