Weekly Mortgage template

[]v[etaphoid]

Anyone know where I can get a mortgage template, such as the one at
http://office.microsoft.com/en-au/templates/TC062062831033.aspx, except with
an option to display and report on weekly payments and balances, rather than
monthly only?

Thanks

 

[Gordon]

Anyone know where I can get a mortgage template, such as the one at
http://office.microsoft.com/en-au/templates/TC062062831033.aspx, except
with an option to display and report on weekly payments and balances,
rather than monthly only?

Thanks

Can't you just change the formulas to reflect weeks instead of months?

BTW - why would you want a WEEKLY report anyway?

 

[]v[etaphoid]

Anyone know where I can get a mortgage template, such as the one at
http://office.microsoft.com/en-au/templates/TC062062831033.aspx, except
with an option to display and report on weekly payments and balances,
rather than monthly only?

Thanks

Can't you just change the formulas to reflect weeks instead of months?

Theoretically, yes. But I'm no excel guru and there's some pretty meaty
formula's in there that refuse to co-operate. I was hoping there might be
someone more accomplished in here.

BTW - why would you want a WEEKLY report anyway?

My loan repayments are weekly with fluctuating additional payments. I want
to be able to set-up an easy to use spreadsheet for the better half to try
and demonstrate the effects of ploughing extra cash into our weekly
payments, rather than her retail therapy.

Cheers.

 

[Gordon]

Can't you just change the formulas to reflect weeks instead of months?

Theoretically, yes. But I'm no excel guru and there's some pretty meaty
formula's in there that refuse to co-operate. I was hoping there might be
someone more accomplished in here.

OK. Here's what you need to do:
In Cell D11 you need to edit the formula so it becomes this:
=IF(Values_Entered,-PMT(Interest_Rate/52,Loan_Years*52,Loan_Amount),"")

Change the formula in D12 to this:
=IF(Values_Entered,Loan_Years*52,"")

In cell B19, change the formula to this:
=IF(Pay_Num<>"",DATE(YEAR(B18),MONTH(B18),DAY(B18)+7),"")
and copy that down to the bottom.

I think that will do it - the only anomaly I can see is that the number of
actual payments doesn't equal the number of scheduled payments. I don't know
why that should be...

HTH

 

[Gordon]

Can't you just change the formulas to reflect weeks instead of months?

Theoretically, yes. But I'm no excel guru and there's some pretty meaty
formula's in there that refuse to co-operate. I was hoping there might be
someone more accomplished in here.

OK. Here's what you need to do:
In Cell D11 you need to edit the formula so it becomes this:
=IF(Values_Entered,-PMT(Interest_Rate/52,Loan_Years*52,Loan_Amount),"")

D11 should be this:
=IF(Values_Entered,-PMT(Interest_Rate/12,Loan_Years*52,Loan_Amount),"")

Not what I put above - that will cure the Actual payments not being equal to
the scheduled payments...

HTH

 

[]v[etaphoid]

Can't you just change the formulas to reflect weeks instead of months?

Theoretically, yes. But I'm no excel guru and there's some pretty meaty
formula's in there that refuse to co-operate. I was hoping there might be
someone more accomplished in here.

OK. Here's what you need to do:
In Cell D11 you need to edit the formula so it becomes this:
=IF(Values_Entered,-PMT(Interest_Rate/52,Loan_Years*52,Loan_Amount),"")

Change the formula in D12 to this:
=IF(Values_Entered,Loan_Years*52,"")

In cell B19, change the formula to this:
=IF(Pay_Num<>"",DATE(YEAR(B18),MONTH(B18),DAY(B18)+7),"")
and copy that down to the bottom.

I think that will do it - the only anomaly I can see is that the number of
actual payments doesn't equal the number of scheduled payments. I don't
know why that should be...

HTH

Thanks for the advice - it looks like that gets us half the way.

I'll have a play with the actual payments result as well and see if I can
figure out how to get it calculating the right number. Also, it looks like
some of the other values such as the total of early payments and interest
don't calculate correctly.

Thanks for your assistance so far - much appreciated.

 

[Gordon]

I'll have a play with the actual payments result as well and see if I can
figure out how to get it calculating the right number. Also, it looks like
some of the other values such as the total of early payments and interest
don't calculate correctly.

Thanks for your assistance so far - much appreciated.

See my other reply....

 

[]v[etaphoid]

Can't you just change the formulas to reflect weeks instead of months?

Theoretically, yes. But I'm no excel guru and there's some pretty meaty
formula's in there that refuse to co-operate. I was hoping there might
be someone more accomplished in here.

OK. Here's what you need to do:
In Cell D11 you need to edit the formula so it becomes this:
=IF(Values_Entered,-PMT(Interest_Rate/52,Loan_Years*52,Loan_Amount),"")

D11 should be this:
=IF(Values_Entered,-PMT(Interest_Rate/12,Loan_Years*52,Loan_Amount),"")

Not what I put above - that will cure the Actual payments not being equal
to the scheduled payments...

HTH

Cheers - we're getting there. Unfortunately, something else is broken since
it no longer correctly calculates the correct number of payments, in D6 or
Rows 18 and lower.

Your assistance is greatly appreciated, Gordon. Thanks again.

 

[Gordon]

Cheers - we're getting there. Unfortunately, something else is broken
since it no longer correctly calculates the correct number of payments, in
D6

D6 is a field you enter - leave it as number of years


or

Rows 18 and lower.

Works OK here....

 

[*alan*]

Can't you just change the formulas to reflect weeks instead of months?

Theoretically, yes. But I'm no excel guru and there's some pretty meaty
formula's in there that refuse to co-operate. I was hoping there might
be someone more accomplished in here.

OK. Here's what you need to do:
In Cell D11 you need to edit the formula so it becomes this:
=IF(Values_Entered,-PMT(Interest_Rate/52,Loan_Years*52,Loan_Amount),"")

D11 should be this:
=IF(Values_Entered,-PMT(Interest_Rate/12,Loan_Years*52,Loan_Amount),"")

Not what I put above - that will cure the Actual payments not being equal
to the scheduled payments...

As I'm involved in the mortgage industry, I was rather intrigued by the OP's
statement that he was making weekly mortgage payments. While 20 or more
years ago some lenders were offering bi-weekly mortgages, those were quickly
abandoned. The vast majority of mortgage loans demand *monthly* payments
and interest is calculated on a *monthly* basis. While there are a number
of services which will make weekly *withdrawals* from your account, the
actual *payments* are made on a monthly basis. If the OP has a true weekly
mortgage, he has a rare bird indeed.

That having been said, the difficulties that he is experiencing with trying
to use that template for weekly payment calculations lies in the fact that
the template is still making the assumption that pmts are expected on a
monthly basis and that interest is calculated on a monthly basis.
To illustrate: a $300,000 loan for 30 years at 5% will have a monthly pmt of
1610.46; total number of pmts is 360.
Entering in B19 =IF(Pay_Num<>"",DATE(YEAR(B18),MONTH(B18),DAY(B18)+7),"")
if you then substitute in D11 the first formula you suggested:
=IF(Values_Entered,-PMT(Interest_Rate/52,Loan_Years*52,Loan_Amount),"")
you'll come up with a "weekly" pmt amt of 371.39 which is reasonable -- only
about 0.25 short of (1610.46*12) / 52. And the scheduling shows a weekly
progression. So far, so good.
HOWEVER, one quickly notices that the Principal Payment column shows
negative
figures and the ending balance *increases* as "pmts" are made.
If you substitute in D11
=IF(Values_Entered,-PMT(Interest_Rate/12,Loan_Years*52,Loan_Amount),"")
your "weekly" pmt increases to 1251.91 which is roughly the monthly amount
prorated to a pmt every 23 to 24 days. Even though not a weekly amount,
it's better --- but still not good enough, as you'd be paying down your
principal vastly more slowly than standard monthly pay-down.
You'll notice also, in both cases, that the scheduled number of pmts for
this loan, even though ostensibly scheduled on a weekly basis, still number
only 360, rather than the 1560 you'd expect for 30 years.
The underlying problem lies with the template's assumption of monthly
payment expectations and monthly interest calculation.

 

[Guest]

I'll have a play with the actual payments result as well and see if I can
figure out how to get it calculating the right number. Also, it looks like
some of the other values such as the total of early payments and interest
don't calculate correctly.

Thanks for your assistance so far - much appreciated.

See my other reply....


I too 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