November 30, 2021
Real Estate Financing and Investing/Latest Mortgage Options

Microsoft Workplace/Mortgage Cost Amortization Schedule


The Scholar Will Be Ready To (TSWBAT):

  • Format sections of a worksheet for simple readability
  • Create International references and use them in a formulation
  • Decide the month-to-month cost of a mortgage with the PMT operate
  • Decide the current worth of a mortgage utilizing the PV operate
  • Analyze knowledge in a worksheet
  • Create an Amortization schedule
  • Create hyperlinks in a worksheet
  • Set a print space and print a part of a worksheet


Hyperlink – hyperlinks from one doc to a different, net web page to a different, or a part of a doc to a different a part of the identical doc

International Cell Identify – A reputation given to 1 cell in a workbook, that can be utilized in any sheet

PMT Operate – determines the month-to-month cost on a mortgage based mostly on the speed, cost quantity and mortgage quantity.
-PMT(Monthly_interest_rate/12, 12*years, loan_amount)

Knowledge Desk – a variety of cells that exhibits the solutions to a formulation

  • one-input knowledge desk – one worth within the formulation varies
  • two-input knowledge desk – two values within the formulation range
  • Enter Values – The values which might be put into the formulation for a knowledge desk

Amortization Schedule – A desk of values that exhibits the start steadiness of a mortgage, ending steadiness for a mortgage, quantity paid towards the precept, and quantity paid towards the curiosity on the finish of every yr.

PV Operate – determines the current worth of a mortgage, or how a lot you continue to have to pay on the finish of a given period of time

Annuity – a sequence of mounted funds similar to your month-to-month funds on a mortgage

Cost Calculator[edit]


  • Titles
    • Sort “Cost Calculator” in cell B1
      • Merge and Middle cells B1:E1
      • Format the font as you want – dimension 16pt
    • Sort “Date” in cell B2
    • Sort “Merchandise in cell B3
    • Sort “Worth in cell B4
    • Sort “Down Cost” in cell B5
    • Sort “Mortgage Quantity” in cell B6
    • Sort “Charge” in Cell D2
    • Sort “Years” in cell D3
    • Sort “Month-to-month Cost” in cell D4
    • Sort “Whole Curiosity” in cell D5
    • Sort “Whole Value” in cell D6
  • Formatting
    • Make every column as extensive because it must be for the labels
    • Daring all label textual content
  • Change the sheet identify to “Automobile Mortgage”
  • Save the spreadsheet to your space for storing calling it “Mortgage Calculator”

Operate for At the moment’s Date[edit]

In cell C2 sort:


Format the primary part of your spreadsheet with:

  • an out of doors border
  • column dividers
  • a fill shade
    • Go to the house tab
    • go to the font group
    • click on on the borders drop down button
    • select extra borders
  • ensure that all colours go collectively and you’ll learn the textual content

Format the info as the right sort

  • {Dollars}
    • Worth
    • Down cost
    • Mortgage quantity
    • Month-to-month cost
    • Whole curiosity
    • Whole value
  • %
  • Normal quantity
  • Date

Enter Mortgage Knowledge[edit]

Discover a Automobile you wish to buy and the worth

  • go to Kelley Blue Guide
    • Discover a NEW automobile you wish to purchase
    • Discover the worth
  • go to
    • Discover the rate of interest for a automobile mortgage
    • Bear in mind the variety of years for that rate of interest
  • Decide if in case you have any cash to place as a down cost NOW
  • Sort within the knowledge you will have collected into your spreadsheet
    • C3 = identify of automobile you might be buying
    • C4 = Worth for the automobile
    • C5 = down cost if in case you have one, 0 if you happen to do not
    • E2 = rate of interest for the automobile mortgage
    • E3 = variety of years for the mortgage

Create cell International Names[edit]

  • Click on on the cell you want to identify – C4
  • Go to the Formulation tab
  • Go to the Outline Names group
  • Click on on Outline Identify
  • Be sure the identify is what you need and click on OK
    • C4 = Worth
    • C5 = Down_Payment
    • C6 = Loan_Amount

and many others…

Compute mortgage quantity[edit]

The quantity of a mortgage it’s good to take out is the worth of the automobile minus the quantity you’ll pay while you buy the automobile or the down cost.

  • go to cell C6
  • = value – down_payment


Decide the month-to-month cost[edit]

The month-to-month cost is how a lot you’ll pay every month to repay the worth of the automobile. That is computed with the rate of interest of the mortgage, the years you’ll take to repay the mortgage, and the entire mortgage quantity.

  • go to cell E4
  • -pmt(fee/12, 12*years, loan_amount


Decide the entire curiosity[edit]

The whole curiosity is the entire quantity the financial institution goes to make on loaning you the cash to buy your automobile. For this computation you will want to know the way a lot the mortgage quantity was, and the way a lot you paid in complete over time. That’s figured by taking the years instances 12 to search out the months, after which multiplying by the month-to-month cost.

  • go to cell E5
  • =12*years*monthly_payment-loan_amount


Decide the entire Value[edit]

The whole value of the automobile is how a lot you paid together with the down cost and curiosity. That is figured by including the worth of the automobile with the entire curiosity.

  • go to cell E6
  • =value+total_interest


Analyze your knowledge[edit]

  • Return to kelly blue ebook
  • discover the worth of the identical automobile that’s one yr previous
  • Return to
  • discover the rate of interest for a used automobile
  • Change your knowledge
  • Analyze the distinction in how a lot complete you pay for the used automobile versus a brand new automobile

Curiosity Charge Schedule[edit]


  • B7 = Curiosity Charge Schedule
    • format to similar fonts as B1
    • middle throughout B7:E7
  • B8 = Charge
  • C8 = Month-to-month Cost
  • D8 = Whole Curiosity
  • E8 = Whole Value

make all titles daring

Create a sequence with the fill deal with[edit]

  • B10 = 4.00%
  • B11 = 4.25%
  • settle upon cells B10:B11
  • use the fill deal with to tug down till you attain 8.00%

Add Preliminary Values[edit]

Outline the Knowledge Desk[edit]

  • settle upon B9:E26
  • go to the Knowledge tab
  • go to the Knowledge Instruments group
  • click on on the what-if evaluation drop down
  • select Knowledge Desk
  • click on within the column Enter Cell
  • click on on the speed from cell E2
  • click on on OK


  • settle upon cells B8:E26
  • shade and border much like the part above however a unique shade

Conditional Formatting[edit]

  • settle upon B10:B26
  • go to the HOME tab
  • go to the types group
  • click on on the conditional formatting drop down
  • click on on Spotlight cell guidelines – equal to
  • click on within the format cells which might be EQUAL TO
  • click on on E2
  • click on on OK

Change the speed in cell E2 and watch the colour part change.
Discover the colour fee modifications, however the values within the knowledge desk doesn’t. You would need to re-select the cells and re do the info desk steps.

Amortization Schedule[edit]


  • G1 = Amortization Schedules
    • middle throughout G2:K2
    • similar font color and style as the opposite two sections
  • G2 = yr
  • H2 = Starting Stability
  • I2 = Ending Stability
  • J2 = Paid on Precept
  • K2 = Curiosity Paid
  • Wrap textual content for H2:K2

12 months Collection[edit]

  • G3 = 1
  • use the fill deal with to fill all the way down to G26
  • click on on the good button – auto fill choices
  • click on on fill sequence

Starting Stability System[edit]

Ending Stability System[edit]

  • I3 =PV($E$2/12, 12*($E$3-G3), -$E$4)
  • I4 =IF(H4<=0,0,PV($E$2/12, 12*($E$3-G4), -$E$4))


Curiosity Paid System[edit]

  • K3 =12*$E$4-J3
  • K4 =IF(H4<=0, 0, 12*$E$4-J4)

Fill Desk with formulation[edit]

End the Starting Balances[edit]

Amortization totals[edit]

  • I28 = Subtotal
  • I29 = Down Cost
  • I30 = Whole Value
  • J28 = auto sum column J
  • K28 = auto sum column Ok
  • K29 = C5
  • K30 = J28 + K28 + K29


format the identical as the opposite sections only a totally different shade


Place a Graphic[edit]

  • go to cell H25
  • Discover a image in clip artwork or the web that represents your automobile
  • Go to the insert tab
  • go to image or clip artwork relying on what you might be utilizing
  • Discover your image

Create a Graphic as a hyperlink[edit]

  • Proper Click on on the image and go to Hyperlink
  • Within the handle place the URL for the website online –
  • Discover the online web page for the automobile you might be purchaseing

Set a print space[edit]

  • Choose the realm you wish to print – click on and drag
  • Go to the Web page Format Tab
  • Go to the Web page Arrange group
  • Click on on the drop down for Print Space
  • Click on on Set Print Space
  • Print Preview and you will notice solely that space will print

You may clear the print space or change the print space from this similar drop down.


  1. Edit the spreadsheet you created to purchase your dream residence you discover on
  2. Discover the mortgage share from a financial institution similar to
  3. Determine what sort of mortgage you’ll take out to select the right mortgage rate of interest
  4. Change the graphic on the web page and have it hyperlink to your dream residence from