Mortgage Calculation Question/s

[Guest]

I am trying to create an amortization schedule that includes the following
principles:

* interest calculated daily
* interest "charged" on the first working day of the following month
* payments made bi-weekly
* option for additional payments; both lump sum, and regular small amounts

Is someone able to offer me some assistance?

Many thanks, Ty

 

[Fred Smith]

This won't be particularly easy.

I would attack it by creating a table for each day of your mortgage. Column
headings would be Date, Opening Balance, Payment, Accrued Interest, Interest
Charged, Closing Balance.

Opening Balance is just yesterday's closing balance, except on line 1 it's the
amount of the mortgage.
Payment is whatever payment is made that day
Accrued interest is: OpeningBalance * IntRate / 365
Interest Charged is: if day(openingbalance)=1, sum(accrued interest for previous
month)
Closing Balance is: OpeningBalance - Payment + Interest Charged

The only problem is that a 30 year mortgage is going to take more than 10,000
rows, but at least it will calculate properly.

 

[Don Guillett]

try this to determine daily interest for and interest rate in cell j1
=(1+J1)^(1/366)-1

 

[Don Guillett]

You might also look at the EFFECT formula
=effect(10%,365)

 

[Fred Smith]

The problem with this formula is it compounds the interest daily. The OP wants
interest compounded monthly.

 

[Don Guillett]

* interest calculated daily

 

[Guest]

Thanks everyone for your help.

What I ended out doing was putting general stats at the top of the
spreadsheet; years, number of periods, periodic interest rate, present value,
fortnightly payment.

D1 years
D2 d1*26 (for fortnightly)
D3 interest rate
D4 NEGATIVE value of mortage eg -$100,000
D5 (((D4*-1)/D1/26)+((D4*-1)*D3/365*14))

To work the payments and interest calculations, I haven't stuck to the true
rules of charging the interest on the first day of the month following.
However, I think what I have done is fairly close to reality, and good enough
for decision making purposes. In fact when it comes to drawing down the loan,
I will to a far more detailed spreadsheet with each day per row (I still
can't think of a way of automating this part).

Anyway, in the calculation section I have; period #, total payment, interest
payment, principal payment, extra payments, and balance of loan.

A10 = 0
F10 =d$4*-1

A11 = a10+1
B11 =sum(c11:e11)
C11 =(d$4*-1)*d$3/365*14
D11 =(d$4*-1)/d$1/26
F11 =f10-d11-e11

A12 =a11+1
B12 =sum(c12:e12)
C12 =F11*D$3/365*14
D12 =d$11
F12 =f11-d12-e12

I think copied row 12 downwards to accumulate multiple fornightly payments.

I hope that this helps other people.

Cheers, Ty