loan amortization maths book

pok3rplaya

Hi,

Can anyone recommend me a good kinda introductory finance maths book?

Basically I'm trying to make a really complicated loan amortization table with variable period length, variable rates, the ability to deal with once off lump sum payments etc.

I'm not even sure what that type of maths would be called to be honest so I don't know where to start looking.

I have a solid maths foundation by the way so it doesn't have to be especially basic or anything.

Thanks.

Fremen

You probably just need to know a little about compound interest.

Have a look at the khan academy videos:
http://www.khanacademy.org/?video=interest--part-2#Finance

and the wikipedia page:
http://en.wikipedia.org/wiki/Compound_interest

Once you've got your head around that, it's just variations on a theme and you should be able to get Excel to do the hard work for you.

Yakuza

I used this, back in the days when I was an actuarial student (20 odd years ago ). It's a great grounding in compound interest, yields, amortisation, discounted cash flow etc.
This appears to be the current print : http://www.amazon.co.uk/Introduction-Mathematics-Finance-Step-Step/dp/0750600926 (different ISBN)

As the others have pointed out, there's lots of online resources available too, probably best to start there

Edit: I've attached a very basic spreadsheet showing the amortisation of a €250,000 loan over 20 years, payable monthly. It calculates the monthly payment, and shows how this breaks down into interest and capital payments each month, along with the reducing loan amount. You can see the capital being repaid each month growing (by a factor of the monthly interest rate) and the interest amount decreasing, until at the end of the 20 years, the final balance is zero.

For your purposes, you could add two extra columns, one for the interest rate to apply that month (you'd have to use the monthly equivalent of it (which is (1 + i)^(1/12)), another for any lump sum being paid in that month (with these two pieces of information, you would alter the interest to be paid that month, and hence the capital repaid). You would also have to adjust the number of rows from 240 to the number of months in your calculation.

Finally, instead of the formula to calculate the repayment, you'd have to use the solver add-in to work out what repayment would leave a balance of zero at the end of the term of the loan, taking into account your variable interest rates and variable lump sum payment schedule.

I hope that's helped a bit.

pok3rplaya

Yakuza,

Firstly, thanks for your reply.

I din't mention it in the OP but I've actually been working on this for a while now and I suppose since you made a detailed post I guess I might as well expand a bit. I've attached a spreadsheet showing how far I've gotten so far.

Going from near the left I have a nominal rate for each period of the loan along with it's periodic rate (obviously assuming regular period length and 12 periods per year this is just the nominal rate over 1200).

Then I have columns for payment size ---- by the way the formula behind the PMT function is just c = r / ((1 + r)^N - 1) * -(pv * (1 + r)^N + fv) ) ----, interest portion, principal portion and outstanding balance at end of period. These are all just the same as what you have in your sheet.

Then comes the tricky parts.

What I want is to be able to take account of both period where the borrower is in arrears (ie. payment = 0) and periods where the borrower makes extra payments.

I'd like periods of arrears (and periods of higher interest rates) to work towards increasing the payment amount while keeping the total number of periods steady. Then I'd like extra payments to have the affect of reducing the term of the loan while not affecting the payment amount.

I've attempted to accomplish this in my spread sheet by tracking the "balance" as if there were no extra payments and using this to calculate the size of my periodic payments. I also have a "real balance" column which takes account of lump sum payments.

I multiply the periodic rate by the real balance each period to determine the adjusted interest for said period. Then I take this away from what the payment size would be if there was no increased payments to determine the adjusted paydown for the period. Add any extra payments to this and then take it away from the outstanding real balance at the end of the previous period to get this periods outstanding real balance.

By the way, the adjusted payment is the same as the regular payment except in the case where we try to make a bigger payment than what is left in the outstanding real balance. I could take that column out and it wouldn't make a huge difference.

I made that sound really complicated and convoluted there but it's not that difficult in excel.

This gives an effect similar to what I'm looking for whereby the term of the loan will be reduced by making a lump sum payment at any point. My sheet agrees exactly with this calculator I found too.

However, I'm not sure it's totally correct since inserting a period of higher or lower interest rate somewhere in the term seems to be affecting the length of the term as well as increasing or decreasing the payment size. You can see this if you pick a section of nominal rates and bump them up to 8% and then check in which period the "real balance" hits 0. Also, if you make the "payment due" for the first 6 periods = 0 (representing a deferred start of 6 months), the "real balance" hits 0 two periods earlier than if there is no deferred start (hmm... actually maybe this is correct since it's the same effect as applying the lump sum payment x periods later in the term of the loan? - just thought of that now).

Unfortunately, I've no real world mortgage data and no way to acquire any so I've no way of checking whether what I have here is similar to the calculations that mortgage lenders actually use. Maybe you know something about that?

Anyway, now that I've kicked that off maybe you can help me tweak it a little?

Yakuza

Hi pok3rplaya,
It's quite difficult to model all that at once. If you change interest rates, but keep the payment term constant, then the payments change. If you put in a lump sum (or alter the payments), but keep the interest the same, the term will change. Trying to do the both at the same time leads to a total collapse of the space-time continuum

I would simplfy your spreadsheet to contain the following columns: opening balance, monthly interest rate, regular repayment, lump sum, interest charged, closing balance. If you want to model different scenarios, just use Excel's Scenario Manager (allows you to take snapshots the spreadsheet in various states).

In terms of real world mortgages, with the trackers I have seen, interest is accrued daily (the nominal rate divided by 365 multiplied by the current balance). This is accumulated and added to the balance monthly (around the 28th of the month), so each month's interest figure can have 28, 29, 30 or 31 days' interest (depending on what month it is) accrued. The effect of making a lump sum payment is that, from that day, the calculated interest from that day on will reflect the lower outstanding balance. The banks only adjust the payments when the interest rates change, they don't tend to adjust the payments based on lump sums. That's how it is in my experience, but YMMV.

pok3rplaya

Yakuza wrote: »

In terms of real world mortgages, with the trackers I have seen, interest is accrued daily (the nominal rate divided by 365 multiplied by the current balance). This is accumulated and added to the balance monthly (around the 28th of the month), so each month's interest figure can have 28, 29, 30 or 31 days' interest (depending on what month it is) accrued.

This means that the size of the monthly payment will change depending on how long the current month is right? How confident are you that this is actually what happens?

This means that online mortgage calculators like this one aren't telling you the whole story when they tall you how much your "Monthly Payment" will be. That's only an average (or more likely they're just ignoring the fact that months are irregular periods).

Anyway, I've pretty much solved my problem (I think). New spreadsheet is attached. Note that it too assumes all months are of equal length.

Periods of arrears can be set by setting the "payment instruction" column to 0. Lump sums affecting payment amount can be set in column K. Lump sums affecting term length can be set in Q. The "answers" are columns R through U.