1

I thought I was able to figure this out, but unfortunately my numbers did not work. If someone would please help with this scenario, then that would be greatly appreciated.

Say I'm taking out a $110,000 loan at 5% (compounded monthly) to be paid back on a 60 month term (it will commence on the 1st of the month, so interim does not play a role here). My first three payments are deferred, but I will owe three contact payments of $100 each. How would I properly calculate the amount of the other 57 payments?

My goal is to turn this into an Excel spreadsheet that calculates after plugging in the variables above, but I fear my =PV and =PMT formulas are incorrect. Any insight there, as well, would be very helpful.

Thanks, everyone.

Improve this question
2
  • If you are taking out a loan, the lender will provide you with the amortization schedule. Or is this homework for a course and not really a question about a problem that you are facing in your personal life?
    – Dilip Sarwate
    Commented Mar 4, 2014 at 0:02
  • Ha, I've been out of school too long for this to be homework (which is also why I'm having a hard time remembering how to do this). I have been given an amortization schedule, which is the problem: I am trying to double-check the work since they won't tell me how it was computed, and my math doesn't match theirs.
    – user13380
    Commented Mar 4, 2014 at 17:18

1 Answer 1

Reset to default
2

if I understand the situation, you receive $110,000 on Jan 1; you pay $100 on Feb 1, Mar 1, and April 1; starting May 1 you pay $x each month for 57 months.

The basic idea is that at any time, the time value of the payments is equal to the time value of the original loan.

There is a formula that will give you the present value, in terms of x, of the regular payments on April 1. That's the best time to pick, since you can use the present value of an ordinary annuity.

if you bring the loan amount forward to that point, as well as the three individual $100 payments, using the compound interest formula, you can then equate the loan to the payments, solving for x...

Improve this answer
2
  • Thank you! I was calculating my first payment on day one (using your example, the first payment was Jan 1) rather than Feb 1. My numbers lined up as soon as I changed that. Thanks again.
    – user13380
    Commented Mar 4, 2014 at 17:23
  • Like this but break it into two parts. To use the method DJohnM says, you'll need to apply interest and payments for the first three months to get the PV for April 1 calculation. So, Future value of the three months payments knowing PV, i, and pmt and #pmts. Then that FV value is the PV value on April 1. i doesn't change. # payments remaining is known. Calculate the payment amount over 57 payments given your interest rate and the new PV. Sorry, I just saw the end of DJohnM's post. It's sort of backward though. Easier to crunch if you push the first three months forward to get April's PV.
    – Xalorous
    Commented May 12, 2017 at 21:41

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .