need help with three case scenarios using rate function to calculate interest payments

I need help for this scenario

I have gotten the first scenario but can't seem to get the next two, Questions: 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 document is on the 2nd sheet in the attachment named Goal seek.

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

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