• Skip to main content
  • Skip to primary sidebar

Ryan McCormick

Dedicated Dad, Software Engineer and Lover of Coffee

Simple Mortgage Calculator Function | Excel, Access VBA

May 22, 2014 by Ryan McCormick 1 Comment

There are a lot of ways to calculate a mortgage payment in Excel. I honestly haven’t made any attempts to calculate a mortgage payment in Access using standard functions. There has to be a way, but for fun, I thought that I would share a simple function.

To use this function, insert it into a module. For Excel, make sure you save your Excel document as a “Macro-Enabled” workbook(.xlsm)

VBA Mortgage Calculator Function

The inputs of calcMortgage(Loan Amount, Interest(whole number: 5 for 5%), Length(in years). So for example: $480,000 mortgage at 5% over 30 years equals a Payment of $2576.74. This would be expressed in the function as: calcMortgage(480000,5,30).

Public Function calcMortgage(iPrinc As Double, iInt As Double, iLen As Integer)
    Dim iPayment As Double, j As Double, n As Integer
    'Calculate Monthly Interest
    'in Decimal Form
    j = iInt / (12 * 100)
    'Convert years to monthly periods
    n = iLen * 12
    
    'Calc Payment Amount
    iPayment = iPrinc * (j / (1 - (1 + j) ^ -n))
    
    'Return Payment Amount rounded to
    'two decimal points
    calcMortgage = Round(iPayment, 2)
End Function

As always, please comments with questions, suggestions and improvements.

Related

Filed Under: Microsoft Access, VBA Tagged With: access, Excel, Mortgage Calculator, vba

Reader Interactions

Comments

  1. Israel Francis says

    March 8, 2017 at 7:58 am

    great info! thanks for posting

    Reply

Leave a Reply Cancel reply

Primary Sidebar

Recent Posts

  • Force Quit Kill all Chrome Windows MacOS
  • SOLVED: Angular 6 CLI Karma Stuck in Single Run | Karma Stops Running
  • How to Manually Install Java 8 on Ubuntu 18.04 LTS
  • Remove VirtualBox from Ubuntu 16.04 Xenial
  • Clear all Node Modules Folders Recursively Mac/Linux

Recent Comments

  • KKV on Webstorm adding spaces between imports and braces | JavaScript and TypeScript
  • jusopi on Clear all Node Modules Folders Recursively Mac/Linux
  • Qaisar Irfan on Clear all Node Modules Folders Recursively Mac/Linux
  • mustafa on Remove VirtualBox from Ubuntu 16.04 Xenial
  • Pourya on How to Manually Install Java 8 on Ubuntu 18.04 LTS

Archives

  • May 2019
  • May 2018
  • April 2018
  • March 2018
  • January 2018
  • September 2017
  • August 2017
  • July 2017
  • June 2017
  • March 2017
  • December 2015
  • November 2015
  • July 2015
  • April 2015
  • February 2015
  • September 2014
  • June 2014
  • May 2014
  • April 2014
  • March 2014
  • February 2014
  • October 2013
  • August 2013
  • June 2013
  • April 2013
  • March 2013
  • February 2013
  • December 2012
  • October 2012
  • September 2012
  • August 2012
  • July 2012
  • May 2012
  • March 2012
  • February 2012
  • December 2011
  • November 2011
  • April 2011
  • March 2011
  • February 2011
  • January 2011
  • December 2010
  • November 2010
  • August 2009
  • July 2009
  • May 2009

Categories

  • Angular
  • Angular 2
  • AngularJS (1x branch)
  • Computer Q&A
  • ES2015
  • Internet Marketing
  • Javascript
  • Job Interviews
  • Job Search
  • Karma
  • Laravel
  • Linux
  • Linux/Unix Tips
  • MacOS
  • Microsoft Access
  • Microsoft Excel
  • Microsoft Outlook
  • Microsoft Word
  • News
  • Node
  • Open Source
  • PHP
  • Protractor
  • Resume Writing
  • Spring Boot
  • SQL
  • Ubuntu
  • VBA
  • VBScript
  • VirtualBox
  • Web Development
  • Windows Tips
  • Wordpress

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

Copyright © 2023 · Magazine Pro on Genesis Framework · WordPress · Log in