Here is Mortgage Calculator with Amortization

[sldfghtrike]

So I just wanted a simple calculator that would help me determine ho
long it would be before I finished paying of my mortgage and then I jus
kept adding things and so I came up with this. You may do as you wis
with this workbook. If you have any suggestions or questions feel fre
to to let me know and I'll see if I can do something about it

+-------------------------------------------------------------------
|Filename: Mortgage Calculator.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=510
+-------------------------------------------------------------------

 

[Vacuum Sealed]

So I just wanted a simple calculator that would help me determine how
long it would be before I finished paying of my mortgage and then I just
kept adding things and so I came up with this. You may do as you wish
with this workbook. If you have any suggestions or questions feel free
to to let me know and I'll see if I can do something about it.


+-------------------------------------------------------------------+
|Filename: Mortgage Calculator.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=510|
+-------------------------------------------------------------------+

Hi

Where as this is a handy calculator to have if you have a fixed interest
rate throughout the life of the mortgage, it unfortunately falls over if
you have an interest rate change as once you change the rate in B5 it
has a widespread effect on the overall figures.

I would be more inclined to have a column dedicated to the rate and
applying it to each monthly payment thus localising it to said monthl
and not corrupting previous or post figures.

HTH
Mick.

 

[Stan Brown]

So I just wanted a simple calculator that would help me determine how
long it would be before I finished paying of my mortgage and then I just
kept adding things and so I came up with this. You may do as you wish
with this workbook. If you have any suggestions or questions feel free
to to let me know and I'll see if I can do something about it.


+-------------------------------------------------------------------+
|Filename: Mortgage Calculator.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=510|
+-------------------------------------------------------------------+

And there is also mine at
http://oakroadsystems.com/math/loan.htm#Excel

 

[Bruno Campanini]

Stan Brown formulated on Sunday :

And there is also mine at
http://oakroadsystems.com/math/loan.htm#Excel

Just a first look at your great work...
I'm wondering why you don't use a more precise way in calculating
effective interest rate.
If a bank offer me a loan at 6%/year and then we agree on monthly
payments, I would pay 0.486755056534305%/month, or something like that.

You have followed Excel philosophy:
interest rate/month = interest rate/year diveded by 12.
Which is mathematically and financially wrong.

Bruno

 

[plinius]

Il 29/07/2012 19:47, Bruno Campanini ha scritto:

Stan Brown formulated on Sunday :


Just a first look at your great work...
I'm wondering why you don't use a more precise way in calculating
effective interest rate.
If a bank offer me a loan at 6%/year and then we agree on monthly
payments, I would pay 0.486755056534305%/month, or something like that.

You have followed Excel philosophy:
interest rate/month = interest rate/year diveded by 12.
Which is mathematically and financially wrong.

Bruno

You are absolutely right, Bruno!

E.

 

[CellShocked]

Stan Brown formulated on Sunday :


Just a first look at your great work...
I'm wondering why you don't use a more precise way in calculating
effective interest rate.
If a bank offer me a loan at 6%/year and then we agree on monthly
payments, I would pay 0.486755056534305%/month, or something like that.

You have followed Excel philosophy:
interest rate/month = interest rate/year diveded by 12.
Which is mathematically and financially wrong.

Bruno

Good catch.

Now, take his workbook, and modify it to be correct, and re-submit it
back to him.

No? BTW, did you examine the workbook closely?

 

[Stan Brown]

Stan Brown formulated on Sunday :


Just a first look at your great work...
I'm wondering why you don't use a more precise way in calculating
effective interest rate.
If a bank offer me a loan at 6%/year and then we agree on monthly
payments, I would pay 0.486755056534305%/month, or something like that.

You have followed Excel philosophy:
interest rate/month = interest rate/year diveded by 12.
Which is mathematically and financially wrong.

It's not "Excel philosophy", but the standard way banks in the US do
it.

 

[MrTallyman]

It's not "Excel philosophy", but the standard way banks in the US do
it.

They assess APR and interest accrual DAILY.

 

[Bruno Campanini]

CellShocked explained :

Good catch.

Now, take his workbook, and modify it to be correct, and re-submit it
back to him.

No? BTW, did you examine the workbook closely?

Not too closely indeed, but closely enough to appreciate his work.
In fact I said "Just a first look at your great work..."

I don't want to modify anything written by another person for two
reasons:

1 - I am not requested by the author
2 - I have already done the same thing both using formulas and VBA

Bruno

 

[Bruno Campanini]

Stan Brown used his keyboard to write :

It's not "Excel philosophy", but the standard way banks in the US do
it.

Ok Stan, may I correct my previous statement:
Which is mathematically wrong and financially correct in US,
mathematically wrong and financially wrong in Europe.

BTW Stan, I'm going to study your work with the related literature from
your site.
Then, if you wish, I can send you my better-conceived remarks.

Bruno

 

[Stan Brown]

Stan Brown used his keyboard to write :


Ok Stan, may I correct my previous statement:
Which is mathematically wrong and financially correct in US,
mathematically wrong and financially wrong in Europe.

BTW Stan, I'm going to study your work with the related literature from
your site.
Then, if you wish, I can send you my better-conceived remarks.

Bruno

Bruno, I meant to say in my earlier reply that I suspected our
difference was a matter of different practice in the US versus other
countries.

You are looking at the larger worksheet, right, not the short one
with just formulas?

When I get time, I want to add a column for interest rate in each
payment. Then people could account both for different methods of
computing interest per period from annual interest, and also for
varying interest rates.

 

[Bruno Campanini]

Stan Brown has brought this to us :

Bruno, I meant to say in my earlier reply that I suspected our
difference was a matter of different practice in the US versus other
countries.

You are looking at the larger worksheet, right, not the short one
with just formulas?

When I get time, I want to add a column for interest rate in each
payment. Then people could account both for different methods of
computing interest per period from annual interest, and also for
varying interest rates.

Ok Stan, let me know when you are ready with the upgrade.
In the mean time I'm going to upgrade my works on the matter (Functions
and VBA) translating to english (I'm italian) labels, info, etc.
Then I would submit the workbooks to somebody for correcting their
english.

Regards
Bruno

 

[Stan Brown]

And there is also mine at
http://oakroadsystems.com/math/loan.htm#Excel

I've updated the "Loans ad lib" calculator to include several
additional scenarios, such as biweekly or twice-a-month payments.
It's at

http://oakroadsystems.com/math/loan.htm#Complicated

I've added several columns to make the calculations more transparent,
which should also make it easier to modify for the many scenarios it
doesn't handle right out of the box.