My Assistant
Custom Search

Rate Function Malfunction? Stumped, Access 2007 
Sep 14 2017, 02:40 PM Post#1  
Posts: 163 Joined: 1June 10  In a report, I am trying to calculate growth. I have P0 and P1 for present value and future value, where p1*1 is used in calculation. There is no payments, so pmt=0 I calculate change in days, then divide by 365 (or could use 360 interchangably) to give me Years (or N) which is displayed in Text18; where I solve for RATE. Here is my statement: =Rate([Text18],0,[p0],([p1]*1)) Well, I checked this using my trusty HP12C Financial Calculator using one of the results: P0=57500 P1=90000*1 N= 0.52 HP says I (rate) = 108.70% however, my report says that Rate = 138.65% If I key into the HP: n=0.52, pv=50,000, i=138.65, and solve for FV, HP says FV=86049; and this is about 4k short of what it needs to be. If I key into the HP: n0.52, pv=50,000, i=108.7, and solve for FV, HP says FV=78,262; which is even more wrong. FWIW  I test this in Excel, to solve for I; and it says that I = 136.69%; but if I key into the HP: FV=90,000*1, n=0.52, i=136.69; PV = 52,607.34 which is <> 57,500 So, if I buy an investment for $57,500 and then sell for $90,000 in just over six months, what is the correct growth rate of my investment?  Self taught newbie just trying to remember that fields are in tables and controls are on forms. 
Sep 14 2017, 03:24 PM Post#2  
Posts: 163 Joined: 1June 10  That was Part One of my problem. The calculated Rate is being stored in field = Text24 I have many different investments, each with their own appreciation/degreciation growth +/ value in Text24. Here is function in Text24: =IIf([sd0]=[sd1],0,Rate([Text18],0,[p0],([p1]*1))) note: sd0  sd1 are sale date comparisons. If value = 0 then growth = 0; whereas if sd0<>sd1, then proceed to evaluate with a RATE operation. At end of report, I'd like to get an AVERAGE of those values, if possible; and =Avg([Text24]) is compelling me to enter a Parameter Value for this. Not what I desire. Headache is pounding full force now. Any help would be appreciated.  Self taught newbie just trying to remember that fields are in tables and controls are on forms. 
Sep 15 2017, 06:56 AM Post#3  
Posts: 163 Joined: 1June 10  This is math. Solving for I (or Rate), given FV, PV, and N should always provide a consistent answer. Given any 3 of these variables, you can solve for 4th Problem is not in FV or PV because these are known/given variables. N is always in years, not months days or decades. Although many cells have an N*12 for monthly payments in a year; the same calculation will have an (irate/100)/12 to adjust for this. This is for monthly payments and compounding 12x year instead of 1x per year. If N is not multiplied *12, then I is not divided by 12. (For problem above, no 12's were introduced) The goal of solving for I is that it renders an Annual Rate of interest; which factors in change in value and change in time (holding period). I am testing these numbers using 3 different platforms, Access 2007, Excel 2007, and my financial calculator HP12c. If I round the solution to just 2 decimal places, the answer should be the same on every platform. Across every platform, the value for FV is always entered as a negative; because that's the way it works in the device. I recognize and account for this, and I am consistent. Below is spreadsheet output, with only differences are in N value and the resulting return based upon the holding period. Lose money 2x, break even, make money 3x. (Sorry it didn't paste very well, but I think you can get gist of it) PV 100000 100000 100000 100000 100000 100000 N 1 1 1 1 1 1 I 25.00% 10.00% 0.00% 10.00% 25.00% 40.00% FV 75000 90000 100000 110000 125000 140000 PMT 0 0 0 0 0 0 N Value 1 Where N=whole years PV 100000 100000 100000 100000 100000 100000 N 3 3 3 3 3 3 I 9.14% 3.45% 0.00% 3.23% 7.72% 11.87% FV 75000 90000 100000 110000 125000 140000 PMT 0 0 0 0 0 0 N Value 3 PV 100000 100000 100000 100000 100000 100000 N 0.5 0.5 0.5 0.5 0.5 0.5 I 43.75% 19.00% 0.00% 21.00% 56.25% 96.00% FV 75000 90000 100000 110000 125000 140000 PMT 0 0 0 0 0 0 N Value 0.5 Getting back to original problem with N=0.52 (just a day or two over 6 months); in Excel here is what I get: PV 57500 N 0.52 I 136.69% FV 90000 PMT 0 where HP12c says I=108.6957% If I change N to 1.0; I=56.5217% on HP; and in Excel it says 56.52% This tells me the problem is in dealing with fractional years. Remember, the givens are PV, FV, and N; solve for I=growth rate per year. The thing is, this is all math and the answers should be the same across all platforms if given the same variables. I am dealing in real estate here. Normal people buy low, live in, and sell higher many years later. Other "not normal" people, like flippers, will buy low, modify for a month or two, then sell for 3 or 4x what they paid for it, earning astronomical returns on their short term investment. I'm trying to calculate these rates and I'm having a hard time doing so consistently. Please advise.  Self taught newbie just trying to remember that fields are in tables and controls are on forms. 
Sep 22 2017, 09:29 AM Post#4  
Posts: 163 Joined: 1June 10  Or  looking at the components of this formula: A = P(1+r/n)^(Nt); how can I solve for r? Given P=PV, little n = # compounding times per year BIG N = times that compounding actually occurs A = FV and t=time in whole years I do not know how to isolate the value of r in this problem. Solve for r, using the following givens: A=8235.05 P=5000.00 N=n=12 t=10 solve for r. (Instructors edition says r=5% per year, but please show work)  Self taught newbie just trying to remember that fields are in tables and controls are on forms. 
Sep 22 2017, 04:23 PM Post#5  
Posts: 1,730 Joined: 10February 08 From: Ottawa, Ont, Canada; West Palm Beach, FL  You seem to be answering/adding to your own thread. It's been a long time since I did any "ciphering"/math, so my advice is to use google and youtube etc. with terms like future value, present value, rate of return, periods I found this which is a start and it's free  http://financeformulas.net/Future_Value.html There are many subjects and formulas at https://brownmath.com/bsci/loan.htm#Formulas Good luck  Good luck with your project! 
Sep 26 2017, 10:34 AM Post#6  
Posts: 163 Joined: 1June 10  Thanks Orange, but that doesn't accomplish my goals. I know how to calculate R using my financial calculator, and I can do it in Excel and Access. All of these tools are seeking RATE via iteration; when a precise formula does exist. I want to derive the rate. I found this, appx 2/3 way down page: https://brownmath.com/bsci/loan.htm#Cantrell I will be experimenting with this formula over the coming days. For my purposes, here is what I am attempting to do. Given a population of 100 houses that sold within the past year, examine the growth appreciation of each individual house, given current sale date and price and prior sale date and price. Calculate change in time between sales. (Change in time could be years, could be 10 days.) Apparent givens are PV, FV, and N, where N is change in time. Because change in time may vary so widely, I believe daily compounding is best. Calculate growth for each house. Calculate average growth for 100 house population. All of the formulas and examples are too easy, giving nice round years of holding periods and nice consistent interest rates for those analysis; whereas I am working on not only partial year calculations, but sometimes the change in value is positive, neutral, or negative and huge. The formula is tricky for N, because it is used twice. Once for the number of compounding periods in a year and NEXT for the number of times compounding actually occurs. Example: $10,000 Investment pays 12% per year, compounds monthly, held for 300 days; solve for FV; but then turn it around like this: given PV, and FV, and change in time, what rate of interest do you earn? Or.... look at it this way: Suppose your cousin wants to borrow 10k and promises to pay you back in one lump sum the amount of 11k; what rate of interest is he paying? It is not 10% unless the holding period is exactly one year. But suppose he agrees to pay you 11k in 10 days, or 30, or tomorrow, or 2 years  then what rate of interest is he paying you on the loan? IMHO  the answer to the cousin problem should be exactly the same whether posed to an HP12C, Access, or Excel  but it is not. This is why I seek the precise noniterative method of solving this problem. Thanks for trying.  Self taught newbie just trying to remember that fields are in tables and controls are on forms. 
Oct 6 2017, 04:57 AM Post#7  
UtterAccess VIP Posts: 4,432 Joined: 5June 07 From: UK  QUOTE Here is my statement: =Rate([Text18],0,[p0],([p1]*1)) Well, I checked this using my trusty HP12C Financial Calculator using one of the results: P0=57500 P1=90000*1 N= 0.52 HP says I (rate) = 108.70% however, my report says that Rate = 138.65% Offhand, I don't understand why the P0 and P1 values are respectively positive and negative. I would have thought they should both be positive. Anyway, If you are trying to find an interest rate that changes 57500 into 90000 in .52 years, then 108.7% is correct. ie an interest rate of 108.7%, not 8.7%. ie 57500 * 1.087 * .52 (years) = 32500 approx. I am not sure about the detail of your function, though. Is your rate() function supplied, or one of your own? Calculations like compound interest, NPV, IRR, Annuities and so on use similar ideas to manage a series of payments. It may be your formula is not constructed correctly, or is not actually the correct formula to use. These financial functions are well understood, and I am sure you can easily locate a VBA function to achieve whatever you need.  Dave (Male) (Gemma was my dog) 
Oct 6 2017, 05:26 AM Post#8  
UtterAccess VIP Posts: 4,432 Joined: 5June 07 From: UK  A = P(1+r/n)^(Nt); The reason you have n and N is to do with compounding. If you have a $100 invested at 12% paid annually, then at the end of the year you receive $12 interest, and you have $112 if it's paid monthly, then the monthly interest rate is 1% so if n=1 and the term is 1 year then we get simply A = P(1+ r), and with an r of 5 percent, say, you end with 105% of the original sum. So at the end of month 1, you get 1% interest, and you now have $101 at the end of month 2, you get 1% interest on $101, and you now have $101.01 at the end of 12 months, you end up with a bigger figurer than the first examples. This is a logarithmic expansion, which is why you get a "real" interest rate of r/n, a frequency of Nt, and a power ^ expansion. n is the annual interest per year, and N is the number of years. For a 1 year calculation, they are both 1, and can be ignored. If interest is paid an infinite number of times, then the compound interest rate approaches the value of e (which is natural logarithmic expansion) see this  the section on compound interest is relevant. https://en.wikipedia.org/wiki/E_(mathematical_constant) I am sure you can rearrange the interest equation to give a solution in terms of r, as there is only one r included in the expression. You will need to take an Nt th root of A, I think, to simplify the RHS. if you have several periodic payments, as in a mortgage, or annuity, then you have a sum of calculations, each depending on the previous value, which requires calculus to solve.  Dave (Male) (Gemma was my dog) 
Custom Search

Search Top LoFi  22nd February 2018  05:35 AM 