I’m a first year Finance student and I don’t have the necessary financial calculator yet…I’m trying to use Excel to create a calculator that will work to calculate “r” in the present value annuity formula. If I’m using the PV equation PV = (coupon payment/r)*(1-(1/(1+r)^t)) + Par Value/(1+r)^t and I’d like to solve for “r”. I haven’t found a function that works properly, and I haven’t been able to isolate “r” such that I can just use the basic math functions…if anybody has anything created already, I would appreciate the help. As this is my first post, I’m looking forward to reading this board and gaining knowledge on my way to taking the CFA. Thanks newbie
try RATE function RATE(t,Coupon Payment, PV, Par Value)
Google is your friend
If you have all of the other variables just use the ‘Goal Seek’ function. In column B put the PV In column C put the Coupon Payment In column D put the number of periods (t) Leave column E blank In column A use your formula with cell references to the appropriate columns then do a goal seek on column E.
Wow, Goal Seek, why did I not think of that?!? Thanks! and RATE did not work…wouldn’t return anyvalue for me. newbie
Did you enter the PV as a negative number using the RATE function? Ex: an annuity pays 5 payments of $1000 and costs $3500. What is r? =RATE(5,1000,-3500) calcs 13.20%
KrukVT Wrote: ------------------------------------------------------- > Did you enter the PV as a negative number using > the RATE function? > Well, that explains why RATE wasn’t working for me. Thank you all for your help. I now have two solutions built. newbie
Doing TVM problems on spreadsheets are a good way to get more familiar with Excel. But for the long term, get your calculator and learn how to use it. For most simple (and many complicated) problems, there’s nothing faster and easier. And you’ll be able to use it on exams.