mortgage payment

[Guest]

I would like a template or someone to tell me how to set up the formulas that
would figure out the remaining loan balance after each payment. It's owner
financed with interest calculated monthly, but I try to make weekly payments.
I haven't done so every week though. What I want excel to do is apply the
payments towards the balance weekly while still figuring the interest
monthly. I have no idea how to set up the formulas for this. Can anyone help
or is this not enough information? thanks

 

[Guest]

you can create your own by using the folowing formulas..
=cumprinc() - creates a decending princible balance
=cumipmt() - creates a decending intrest balance
you will have to copy down for the length of the loan for each formula
calculates off the previous loan balance. other amounts can be calculated.
for example...
principal payment this month = previous loan balance - current loan balance.
intrest payment this month = payment - principal payment this month.

I did this for my morgage and found it to be reasonably accurate. off from
the morgage company's numbers but only by pennys.
look up the 2 formulas above in xl help for details on how they work. Thats
what i did.
i don't know but you can post in templates to see if there is a template of
this.
good luck
FSt1

 

[joeu2004]

I would like a template or someone to tell me how to set up the formulas that
would figure out the remaining loan balance after each payment. It's owner
financed with interest calculated monthly, but I try to make weekly
payments. I haven't done so every week though. What I want excel to
do is apply the payments towards the balance weekly while still figuring
the interest monthly.

It is not clear to me what you think should happen. I seriously doubt
that the lender will apply the early payments to the principal only,
then charge interest only at the end of the month based on the
outstanding balance after applying the early payments. If he did
that, the lender woud be losing interest.

I believe the lender will do one of two things:

1. Accumulate early payments, and apply the total payments at the end
of the month after computing interest based on the previous month's
outstanding balance.

2. Compute and apply interest accrued between each payment (i.e.
cumulative daily interest).

I am told that the first (#1) is very common. It is beneficial to the
lender because he gets free use of some of the money early. It is
unclear to me why you would do that, other than on an exception
basis. You lose the savings interest (or investment yield) you would
have earned on the money if you had not made early payments. The
second (#2) is beneficial to you; for that reason, I doubt that a
lender would do it <wink>.

In any case, you should ask your lender what he would do.

What you describe is closest to #1. If that is what your lender does,
you should be able to take any properly-designed monthly template, and
in the payment column, simply replace the formula in the cell with the
sum of all the payments made since the previous due date (e.g.
=100+200+150).

If you would prefer to fill in each payment date and amount and expect
the template to do the sum and apply it on the due date automagically,
well, that is another matter altogether.

You can find a simple template that Microsoft provides ("loan
calculator with extra payments") by doing the following. (Note: This
is not an endorsement of that template. Personally, I believe it is
flawed. But it's a start.)

First, be sure that online Help content is selected. Press F1 and
click on Online Content Settings. If "Show content and links from
Microsoft Office Online" is not selected, select it, then exit and
reload Excel.

Now, click on F1, select Search Results from the pulldown menu next to
the "X", select Templates from the pulldown menu, enter "loan
calculator" in the search field, click on "Loan calculator with extra
payments", and click Download.

HTH. Post back if you have follow-up questions or comments.