Loan Amortization with Daily Calculation

Question

I'm trying to solve this Loan Amortization Question.
Principal = $125000
Interest = 8.5%
Compounded semi annually
Duration is 10 years (120 months)
Monthly Payments Loan given on 7/31/2009
First payment to be received on 8/31/2009

Create First couple lines of the loan amortization schedule.

I was able to find the Interest, But I'm having trouble calculating the Payment Per Month.

Using the information on this page, Days in the month affecting loans? Daily rate came out to be 0.022808998%

Interest1 = ((1+0.00022808998)^31 - 1 ) * 125000 => 886.89 which is correct! 

Now I'm stumped on payments. If I can get help to calculate it please.

Answers are here http://snag.gy/LZtId.jpg

Answer 1

It's very unlikely that you are supposed to take into account the daily rates, otherwise the question would have to specify whether the 8.5% rate was for a leap year or a non-leap year.

Taking the normal approach, that the year is divided into twelve equal months, you have :-

principle is £125000
annual rate is 8.5% compounded semi-annually
term is 10 years
monthly payments

Calculating the effective annual rate (e) ...

ref: http://en.wikipedia.org/wiki/Effective_interest_rate#Calculation

i = 0.085
e = (1 + i/2)^2 - 1

Now finding the monthly rate :-

r = (1 + e)^(1/12) - 1
n = 12*10

and using a loan payment formula ...

ref: http://www.financeformulas.net/Loan_Payment_Formula.html

p = r*pv/(1 - (1 + r)^-n) = 1540.03

Check

Final Note

If you really wanted to used daily rates and take into account the varying number of days in the months, and end up with a fixed amount paid monthly, here is a simplified example calculation over three months.

You would need to calculate the daily rate (d), then calculate the various monthly rates and solve the sum as shown. To account for leap years would a further step, left to the reader.

Answer 2

There is no simple way to calculate the monthly payment with allowance being made for the different number of days in individual months. Because the payments are not made at regular intervals (in terms of the compounding period), there is no simple formula for finding the size of the regular payment.

You have found the appropriate daily compounding rate. One way of finding the monthly payment is to make up a daily amortization schedule. Just set up a spreadsheet with around 3700 rows, each row representing one day in the 10-year payment schedule. Put a guess for the monthly payment in a cell near the top. Start with a balance owed of $125,000.00, Each day you take the precious day's balance, add one day's interest on that balance, and subtract any payment (referencing the guess in the cell). Repeat for the 120 payments. Note that the existence of a payment on any day depends on the actual date; your example is complicated because the "End of the Month" is not a fixed Day-of-the-Month Number. Payments on, say, the 15th would be easier to program.

Now fiddle with the value of the guess, until the balance owed after payment #120 is as close to zero as you can get it.

Good news: Excel has a function, Data - What-If - GoalSeek that will do the guessing for you.

Bad news: The result doesn't match the value in the answer you cite...