Question

3. The IRR is the rate which makes the principal at the beginning of the year following the last cash flow is paid out equal to 0. This is shown in the table below. Use the Goal Seek function of Excel to find this rate.

	A	B	C	D		E	F	G	H
1	IRR?	3.00%
2				LOAN TABLE
3	Year	Cash flow		Year	Principal at beginning of year		Payment at end of year	Interest	Principal
4	0	-800		1	800.00		300.00	24.00	276.00
5	1	300		2	524.00		200.00	15.72	184.28
6	2	200		3	339.72		150.00	10.19	139.81
7	3	150		4	199.91		122.00	6.00	116.00
8	4	122		5	83.91		133.00	2.52	130.48
9	5	133		6	-46.57		ß Should be zero for IRR

Check the value that you get and compare it to the Excel IRR function.

Help me with this please. In Excel using goal seek.

Answer 1

This is a very simple problem of finding IRR using the goal seek function in excel. I will first share the solution excel, then the method of how i applied the goal seek.

This is the solution excel:

Now as you see the solution above. Have a look at column F7 your goal should be to make the value of column F7 equal to 133 (which is your last payment) by changing your IRR which is column B3.

Goal seek function is located in the data tab under what if analysis. The screen shot for inputs to goal seek are: