 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / August 2007Is there a RATE function for single sum?
Is there a built in function for calculating the interest rate for a single sum? IOW, is there a function to calculate i in PV =FV/(1+i)^n ? I can do the math to solve for i but I want to know if there is a function to do it for me. The RATE function is for annuities. NOMINAL and EFFCT each take the other as input. And there are several functions to calculate security yields given specific dates. But all I want is a function to calculate i for a single sum. Is there such a function? >Is there a built in function for calculating the interest rate for a single >sum? [quoted text clipped - 11 lines] > >Is there such a function? Use IRR for a single period, XIRR otherwise. Negate the PV in each case. Thanks WldBill But if I can only input FV and -PV into IRR, then n must be limited to 1. For example, if I start with 100 and end with 500, the IRR formula gives me an i of 400%. Technically this is true, the investment increased by 400%. But this tells me nothing of the time involved. I need an annual i to make it meaninngful. In the example above, if it took 4 years to go from 100 to 500, then the annual i is 49.53%, a big difference from the 400% IRR gives me. To me it is strange that this functionality was omitted from Excel. >>Is there a built in function for calculating the interest rate for a >>single [quoted text clipped - 15 lines] > > Use IRR for a single period, XIRR otherwise. Negate the PV in each case. Perhaps the "Rate" function. =RATE(4,0,-100,500) -> 49.53% I thought "single sum" was for one period also.  SignatureHTH :>) Dana DeLouis > Thanks WldBill > [quoted text clipped - 33 lines] >> >> Use IRR for a single period, XIRR otherwise. Negate the PV in each case. Thank you Dana. Help says RATE is for an annuity but I didn't think to specify a PMT of 0. When you do, Excel treats it as a single sum (lump sum). Thanks again. > Perhaps the "Rate" function. > [quoted text clipped - 40 lines] >>> >>> Use IRR for a single period, XIRR otherwise. Negate the PV in each case. Hi. Glad it helps. Just to mention, one can also omit the 0: =RATE(4,,-100,500) or... =POWER(500/100,1/4)-1 SignatureDana DeLouis > Thank you Dana. > [quoted text clipped - 49 lines] >>>> Use IRR for a single period, XIRR otherwise. Negate the PV in each >>>> case. Check what I said again. To use XIRR, add a pair of dates by the -PV and FV, and make that range the second argument. The third "guess" argument is optional, and you can experiment with its effect, if any. But if you know N, you might as well simply take the IRR ^(1/N) As that goes, the math to avoid using IRR is almost as simple :)
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.