# Mortgage Functions

L

#### Legacy 98055

##### Guest
MortgageCalculator.xls
ABCDEFGHIJK
1
2StartingDateofthisLoan:09/1/2001PresentLoanValue:\$94,124.26
3DateofFirstPayment:10/1/2001PrinciplePaidThrough09/01/02:\$875.74
4InterestRate:7.5%InterestPaidThrough09/01/02:\$7,095.30
5AmortizationPeriod(Months):360TotalInterest:\$144,131.36
6AmortizationPeriod(Years):30TotalScheduledPayments:\$239,131.36
7OriginalLoanValue:\$95,000.00EstimatedMonthlyPayment:\$660.13
8LastPaymentDate:09/1/2002
9
11
12Hypotheticals:
13
14Fromaspecificperiodtoaspecificperiod(30yearwith360months(periods))
151.IfIincreasemymonthlypayment
162.IfIpayalumpsumonanygivendate
173.Ifpaidbi-weeklyorevenweekly
18
19Ifpossible,IwouldliketoreturntheamountofinterestdebtwhichIwouldsave.
20Also,toshowtheamountoftimetheamortizationperiodwouldbereducedby.
21
Sheet1

Thanks,
Tom

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Tom,

The monthly repayment should be \$664.25.

=PMT(.075/12, 30*12, -95000)

or

=ABS(PMT(0.075/12, 30*12, 95000)

I have emailed you with a Loan Calculator (Excel spreadsheet) that allows the option to over-ride the calculated monthly repayment with a payment amount of your choice. The spreadsheet includes an option to pay monthly, fortnightly and weekly (but not bi-weekly), and includes a simple Chart that graphs repayments in months. In reference to bi-weekly payments, you will have to amend the formula.

Surprisingly, despite the difference in the monthly loan amount, all your other calculations are fine (I have highlighted my model in green to shows your figures).

My model does not cater for lump sum payments. Michael Kelly, a mortgage specialist, has devised a number of spreadsheets (in English pounds) that may give you the answer (I don't have the time to go through all his models):

Incidentally, I have no objection to you or Mr. Excel publishing the model as a free download (you may wish to spruce it up, particularly the Chart!). The model include one of Aladin's super formulas that gets the last value in a range.

Kind regards,

Mike

Thanks Mike.
Tom

Replies
1
Views
235
Replies
6
Views
631
Replies
6
Views
295
Replies
7
Views
343
Replies
3
Views
250

1,196,250
Messages
6,014,246
Members
441,808
Latest member
xplainer

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back