1 To return the value of the cell D8, the formula should be OFFSET(A1, ) 2 To set up scenarios, you need to first use to set up a list, then to set up the reference to set up the cells that display the output results from the scenario. cell. Last you need to use Data validation; VLOOKUP; CH0OSE CHOOSE; VLOOKUP; data table Data table; VLOOKUP; CHOOSE Goal seek; data table; VLOOKUP 3 function calculates the repayment for a loan; the The function calculates the interest portion of the loan repayment E A C G H 1 #of periods 2 2017/12/31 2018/12/31 2019/12/31 2020/12/31 2021/12/31 2022/12/31 2023/12/31 2024/12/31 4 FCF -1000 300 400 500 600 700 800 900 Based on the image above, the dynamic formula that finds the XIRR of the 6th period would be: (must use cell B1 to make formula dynamic) ),B3:OFFSET(B3,0, XIRR(B4:OFFSET(B4,0, )) and the output would be B1, B1, 39.7% B1-1, B1+, 36.8% B1-1, B1-1, 34.1% B1+1, B1+1, 43.1 % 4 G F E D. C B A 5x 4x 2x 3x 1x 1 4 3 2 1 1x 2 10 8 6 4 2 2x 3 15 12 9 6 3 3x 4 20 16 12 4 4x 25 20 15 10 5x 6 7 The formula INDEX(B2:F6,MATCH("4x". cell with the red box L0), MATCH("3x", L0) would give the result of the D2:D6, B5:F5 B1:F1, A2:A6 B5:F5, D2:D6 A2:A6, B1:F1