Goal Seek and Rate Function

2 things -

1. please stop posting duplicate threads.
2. Nothing is more frustrating than to be working on homework and to get stuck. Without guidance, you might have no hope of finishing the assignment before the deadline. The Internet can be a big help in that respect, but without learning how to get the right answer, asking others to solve your homework questions for you simply becomes a downward spiral (and it's cheating).

We do not want to contribute to you cheating yourself out of your education, but we also acknowledge that seeking assistance to learn a concept is a legitimate request.

If you are genuinely interested in receiving help in the form of tutoring or coaching, then please rephrase the title of your original post to clearly indicate you are seeking coaching or tutoring help. Any forum members (who are willing to assist as a tutor) will modify their responses accordingly to facilitate your learning. Tutors don't tell you the answers, they help you figure it out for yourself; so don't expect answers, expect suggestions, or just plain hints. Also, be specific in describing the function/formula or technique you trying to learn, and tell us what you have attempted so far. Otherwise, expect your plea for homework answers to be ignored.

Another thought with homework like this, ask specific questions. Let us know what you already know how to do and what specifically you don't know.

I would probably suggest you start here: http://office.microsoft.com/en-us/ex...990.aspx?CTT=1 The example used in the help file is even another loan parameter adjustment example.

There are three questions in your spreadsheet. 1 and 2 should almost answer themselves, once you have found and opened the goal seek dialog. If they don't, then let us know specifically what you don't understand in the Goal seek fields.

Scenario 3 should be relatively straightforward if you will use the RATE() function in the blue cell (as your spreadsheet indicates). If you are unfamiliar with the RATE() function, start here with the help file http://office.microsoft.com/en-us/ex...in=HP010342656 If there is something in here that you don't understand (what the arguments mean, how to reference other cells as an argument, or whatever), let us know and we'll try to help you understand it.

What references did you put in "Set cell" and "By changing cell" fields? What did you put in the "To value" field?

Set cell should indeed by C12, I'm not sure which cell is "monthly payments", could it be that you put an invalid reference for the by changing cell?

B12 contains the text string "monthly payment", which is neither a value nor does it fit into the string of calculations that feed into C12. The "by changing cell" needs to be a cell that contains a value (usually a hand entered value) that is one of the "precedent cells" for the "set cell". In this case, the question asks you to find "how many months" will it take to pay of the loan at a payment of 8000, so I would expect your "by changing cell" will be a cell that contains the number of months or years needed for this scenario.

Correct. And C11 is a function of the no of years (C5) and the number of payments per year (C6), so you need to look at these two cells and decide which one of these two cells should be the "by changing cell".

Does that seem reasonable (that it will take almost a year longer to pay of the loan by paying a smaller monthly payment)?

I need to leave, so I won't be back until later. If you need more help, someone will hopefully be able to jump in and help further.

Just as before, F10 is a formula, not a value. F10 is calculated from F4 and F6, which are both hand entered values.

Based on what we have talked about previously, which one will you have goal seek change -- F4 or F10?

It is unclear whether you are still looking for an answers to the third scenario. And it's unclear which thread we should respond: one thread is marked "solved", and a moderator has complained about the other thread. I will duplicate this response. But you should choose only one thread to continue the discussion.

For my comments below, refer to the worksheet "Scenarios (corrected") in the Excel file "A2-1402-F14-Q2-2-1 MOD.xlsx" attached below.

Originally Posted by MysJee

In this third scenario, I only make a down payment of $20,000 and i'm forced to make payments of $8,000 a month over 4 years. I need to use the Rate function in cell I10 to figure out how much my interest payments are and then fill in the supporting data in cells I4, I13 and I14.

The formula in I10 should be:

I10, periodic rate: =RATE(I11,I12,-I9)

Note that this is a periodic rate. So the formula in I4 (annual rate) should be:

I4, annual rate: =I10*I6

The other formulas should be:

I13, total repayment: =I12*I11
I14, total interest: =I13-I9

If you insist on using CUMIPMT, note the comments about your misuse of it below.

Originally Posted by MysJee

In the first scenario, you only have $8,000 available as a monthly payment; use Goal Seek to see how months it will take to pay off the loan. Format this value using two decimal places.

I know the assignment requires that you use Goal Seek. But FYI, you can calculate the number of payments (C11) directly with the following formula:

C11, number of payments: =NPER(C10,8000,-C9)

Nitpick.... If you do use Goal Seek, the "by changing" cell should be C11, with the formula =C11/C6 in C5 (years).

The reason is: usually, the Goal Seek result is approximate. There is less numerical error by aproximating the number of periods, then dividing by periods per year. In effect, you are dividing (reducing) the numerical error as well.

It makes little difference in this scenario. But the principle might be useful and more important in other examples.

Your use (or the instructor's use) of CUMIPMT in C14 is incorrect. At a minimum, the last parameter should be 0 instead of 1, to wit:

C14, total interest: =-CUMIPMT(C10,C11,C9,1,C11,0)

The reason is: usually, loan payments are in arrears; that is, at the end of the period. [EDIT] Moreover, it is consistent with how you use PMT in C12 and F12.

However, note that in this scenario, the number of payments (C11) is not an integer. In that case, CUMIPMT returns the wrong value(!). In effect, it is as if the call were -CUMIPMT(C10,C11,C9,1,INT(C11),0). That fails to account for interest paid in the last payment, after payment #58 in this example.

The formulas in C13 and C14 should be:

C13, total repayment: =C12*INT(C11)+FV(C10,INT(C11),C12,-C9)*(1+C10)
C14, total interest: =C13-C9

In other words, total interest is total repayment minus total principal (loan amount).

That formula in C13 assumes the payment is at the end the last (fractional) period. I believe that is consistent with common loan practice, since the borrower's cash flow situation might require it. However, that does depend on the lender's policies.

If you want to prorate the interest for the last fractional period, the formula in C13 might be:

=C12*INT(C11)+FV(C10,INT(C11),C12,-C9)*(1+C10*MOD(C11,1))

Originally Posted by MysJee

In the second scenario, you only want to pay a total of $30,000 in interest; use Goal Seek to establish the interest rate you would to negotiate to achieve this. Format two decimal places.

What appears in your Excel file is completely wrong. The total interest is not $30,000.

If the assignment requires that you use Goal Seek, "set cell" should be F14, "to value" should 30000. "By changing" should be F10 (periodic rate), not F4 (annual rate), again to minimize numerical error.

However, FYI, you can calculate the rate directly as follows:

F4, annual rate: =F10*F6
F10, periodic rate: =RATE(F11,F12,-F9)
F12, payment: =(30000+F9)/F11

Again, instead of using CUMIPMT, the formulas in F13 and F14 should be:

F13, total repayment: =F12*F11
F14, total interest: =F13-F9

But if you must use CUMIPMT, remember that the last parameter should be 0, not 1, to wit:

F14: =-CUMIPMT(F10,F11,F9,1,F11,0)

In this case, CUMIPMT returns the correct total interest because F11 (number of payments) is an integer.

The formula for payment in F12 is derived from the simpler formulas in F13 and F14, to wit:

totalInterest = totalRepayment - loanAmount
3000 = F12*F11 - F9
F12 = (3000 + F9) / F11

Right F4 would be the one to change.

Oh ok i will send an attachment of what i have done so far

In the last sheet in the attachment called Scenario Summary Report, i created a scenario manager for each of the three cases and the scenarios will rely on
changes to cells C2, C3, C4 and C5, but the question wants me to create a Scenario Summary report that displays the outcomes in cells C9:C14 in a new
worksheet, which I think i just did, then Clear the information in the “Scenario Summary” worksheet in cells
B17:B19, and retitle cells B6:B9 and B11:B16 appropriately. Apply a Dark Red fill to B2:G3, then Now return to the “Goal Seek and Rate” sheet and use Scenario Manager to create a PivotTable report. Place this after the “Scenario Summary” worksheet. Later, remove the report filter and remove $C$9 and $C$11 from the Values field. Rename A3:E3 “Situation”, “Monthly Rate”, “Monthly Payment”, ``Total Payment`` and “Total Interest” respectively. Widen all the columns so full titles are visible. Then, Apply Percent number format to B4:B6, adding three decimal places, and acct no.
format to C4:E6 with no decimal places. Sort the PivotTable so the “Worst Case” scenario is at the top. Apply a Dark Red fill to A3:E3