Internal rate of return using Numbers
bought a property in 2018 for $700,000
have owned for 5 yrs
no payments made
now valued at $1,200,000
can't seem to get the right formula for the IRR
Help
Question marked as
HI Zr2,
The IRR function calculates the interest rate needed to reach a target amount over a period of time. In your case, you want to determine the effective annual rate which would raise an initial investment (in the house) of $700,000 to a specific value ($1,200,000) over a period of five years, given an initial deposit of $700,000, no further payments, and no withdrawls.
In the calculation, cash flows are recorded as negative amounts when the cash flows from you to the account, and positive when the cash flows from the account to you.
Here's what it looks like in a table:
Column A contains the dates for the beginning of each year, and determines how many one year periods there will be (5).
Column B contains only the initial cost and the five year goal.
Column C records the cash flow for each year—$700,000 out of your pocket on buying the house, $0.00 in each of the following four years, and a $1,200,000 potential withdrawal at the end of the fifth year.
The IRR formula shown below the table is entered in cell D2, where it displays the annual interest rate required to reach the goal.
Here's a second table that uses the interest rate calculated above (rounded to 2 decimal places) to do the calculations in a different manner that should return (close to) the same end amount.
The interest each year is added to the original amount, and the next year's interest uses the result as the amount on which interest is calculated.
The first formula, toward the right, C3, multiplies the amount in B2 by the interest rate in D2, and places the result in C3.
As the formula is filled down to the rows below, B2 increments to always be in the row above the row containing the formula.
The lower formula, placed in B3, gets the amount in B2 and the interest amount in C3, and adds them to get the amount on which interest will be paid for the following year.
As can be seen in B7, the end amount is slightly less that the stated amount in Table 1. The difference is due to rounding the required rate in Table 1 to a slightly smaller value than was calculated as 'needed' to reach the stated amount.
Bottom line is that the two calculations each support the other as being correct, with different results as expected due to the rounding of one value in the calculations.
Regards,
Barry
