-
Hi all. I've been wrestling with this problem for a few hours today, along with some others on my course at university. Basically we have to make a model in the modelling & simulation package Stella.
The model is for an annuity where the customer makes an initial one-off payment of £100,000, and then receives a fixed monthly payment until their death. Assuming the life expectancy is 10 years, the APR is either 4%, 6% or 8%, and the interest is compounded monthly, we have to figure out how much the bank should pay out each month.
So, basically we have to figure out how much the payments should be so that, taking into consideration payouts made and compound interest on the remaining capital, after 120 months the capital remaining will be 0.
It all sounds simple enough but it's quite difficult to find an expression that takes into account the decreasing capital due to payments made. i have come up with a couple of expressions so far, and they don't seem to quite work, although they're close.
Trial and error shows that the payouts should be approx. £1012 per month, or thereabouts.
Can anybody help at all? Everybody else I have spoken to has given a trial and error answer, but I am pretty sure that this can be solved analytically. Perhaps it can be solved iteratively?
Thanks to anyone who can offer any advice :)
-
Okay, I got it sorted out after leaving it til 5am and taking another look at it :)
-
Mortgage payments.
HarryW: With one small caveat, this is the same as the formula used to calculate equal mortgage payments.
The caveat is that mortgage payments always start approximately one month after the bank forks over the money to buy the house. Some banks are picky and calculate an extra small payment or rebate (of a few days interest) if the first payment is not exactly one month after the money is turned over to the borrower.
On the other type of arrangement, the first payment might be due on the same day as the large lump sum is paid. This makes for a slightly different calculation.
The following applies if the first monthly payment is due one month after the lump sum is paid.- n = 12 * NumberofYears)
- I = YearlyInterest/1200
- V = 1 / (1 + I)
- MonthlyPayment = Amount * I / (1 - V^n)
I have a calculator which got the following for 100,000 lump sum, 10 years at 6%: 1110.21 per month if paid one month after settlement; 1104.68 if first payment is immediate. That one month makes a difference.
The mortgage department should be able to come up with these numbers.
Also, the insurance industry has tables which they use so that mortality probabilities are factored into the computations.
-
Ah right. Well I came up with a different formula which seems to give the same answer...
P = (100000(1+i/12)) / Sum[x=0 to 119](1+i/12)^x)
That's the 120-month-specific version, I got a general formula. Thanks though. I derived the above equation myself by looking at the pattern of each term, may I ask how yours is derived?
-
From a book.
I got my formual from a Handbook of Tables and Formulae that I keep handy. I have a small but good collection of technical books. This formula is referred to as the formula for an annuity whose present value is one. You then multiply by the amount.
I suspect that there is a way to sum your formula to arrive at mine.
Many years ago I saw my formula derived as the sum of a series. If summing your series arrives at the same result, I would bet money that there is a way to derive my formula from yours. Your series looks like an arithmetic progression, for which there is some simple formula.
My HP calculator has financial functions built in. I used it for the Immediate payment calculation, for which I do not know the precise forumla.
I find it hard to believe that a bank does not have mortgage payment tables and formulae available. That is what we are dealing with here.
-
Oh I'm sure a bank would do, but this is just a modelling exercise for my university course. We're using Stella, a modelling tool for systems with rates of flow and that sort of thing. It's quite natty actually, or it would be if it didn't have an Apple Mac interface :)
It is a kind of series that I've seen before, although I don't remember what to do with it. I suppose I really ought to look it up. I shall do so in a minute, although I don't have much in the way of maths resources to hand.
Essentially it's a series that looks something like this:
S = 1 + R + R^2 + R^3 + ... + R^n
Looks like a very good candidate for a simpler formula.
-
Having looked in a maths book, it's a simple geometric series.
The book simplifies a general geometric series
s(N - 1) = a + ar + ar² + ... + ar^(N - 1)
rs(N - 1) = ar + ar² + ar³ + ... + ar^N
s(N - 1) - rs(N - 1) = a - ar^N
factorising gives:
(1 - r)s(N - 1) = a(1 - r^N)
s(N - 1) = a(1 - r^N) / (1 - r)
which is, I suspect, where your formula comes from.
