Apple Numbers Intrate Function Malfunctioning

RobCJohnson69 wrote:

give it a present value (PV) of $100 and a future value (FV) of $161.051 and 5 years as the term it returns 10% as the annual compound interest rate which is the correct answer

If you want to use a built-in function then RATE comes the closest. It is most often used in situations with periodic cash flows (which you don't have) and seems to introduce a small rounding error when you set the periodic cash flow to 0 in this example.

IRR also can produce the same result though you need to have cells to hold cash flows, and put 0 in them.

INTRATE is typically used for discounted securities (often with short maturities) that have no periodic cash flows and is therefore not suitable here.

I typically use the general formula for CAGR, also sometimes called "geometric mean rate," as suggested by muguy above and illustrated below.

Note that all these finance functions and formulas have internal assumptions about reinvesting, which may or may not precisely model the real world. When you look under the hood things quickly get confusing and complex, even to practitioners. That's how some of us finance guys make money!

=(F2/A3)^(1/G3)−1

SG

The RATE function does not seem to be quite as accurate for this purpose as doing it with the simple math formula that muguy provided. It probably uses iteration because the purpose of the function is for a more complicated situation. I'd use the math formula.

RATE(5,0,−100,161.051) gives 9.99999999%

The math formula gives an exact 10%

The formula is working as expected. Intrate returns the effective annual interest rate for a security that pays interest at the end of a fixed time. So, using i = Prt you get $100 * 12.21% * 5 =$61.05

see Numbers - Function list - Apple (AU)

you need compound interest rate, =P*(1+R/T)^(N*T)

P - principal

R - rate

T - Times compounded per year

N - number of years

You can just math it out from the original formula

rate = (End Value / Start Value)^(1 / # of Periods) – 1

(([End Value]/[Start Value])^(1/[# of periods]))– 1

or, you can try the rate function, which should provide what you need.

I’ve always used the formula provided. You can also use the future value function.

=fv(rate/periods per year, years*periods per year, 0, -present value)

=fv(10%/1,5*1,0,-100)