If I were to purchase a home for 575,000 at 5 3/4% :confused: for 30 years, what would my payments be? What would the interst amount per month and the principal amount.

Any ideas on how I would enter this into am Excel spreadsheet?

Thanks.

Printable View

- Jun 5th 2007, 07:09 PMSuwaneeAmortization help
If I were to purchase a home for 575,000 at 5 3/4% :confused: for 30 years, what would my payments be? What would the interst amount per month and the principal amount.

Any ideas on how I would enter this into am Excel spreadsheet?

Thanks. - Jun 5th 2007, 08:15 PMCaptainBlack
I expect there is some potted formula for theis, but we can also work it out

from first principles.

Let the principal be $\displaystyle P = \$ 575000$ and $\displaystyle \rho = 1+0.0575/12 = 1.004792$ be one more than the monthly interest rate, and the monthly repayment be $\displaystyle a$.

Then after 1 month our outstanding debt is:

$\displaystyle

D(1) = \rho P - a

$

after 2 months:

$\displaystyle

D(2) = \rho (\rho P - a)-a = \rho^2 P - a(1+\rho)

$

and after $\displaystyle n$ months:

$\displaystyle

D(n) = \rho^n P - a(1+\rho + ... + \rho^{n-1})=\rho^n P - a\frac{1-\rho^n}{1-\rho}

$

Now 30 years is 360 months and after this time our debt will be zero so:

$\displaystyle

D(360) =\rho^{360} P - a\frac{1-\rho^{360}}{1-\rho} = 0

$

or:

$\displaystyle

a = \frac{\rho^{360} P (\rho-1)}{\rho^{360}-1} \approx \$ 3355.69

$

RonL