{"id":2890,"date":"2017-10-11T01:02:03","date_gmt":"2017-10-11T01:02:03","guid":{"rendered":"http:\/\/excelvbatutor.com\/?page_id=2890"},"modified":"2020-04-23T10:51:29","modified_gmt":"2020-04-23T10:51:29","slug":"excel-vba-lesson-10-financial-functions","status":"publish","type":"page","link":"https:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-10-financial-functions\/","title":{"rendered":"Excel VBA Lesson 10: Financial Functions"},"content":{"rendered":"\n<h4 class=\"wp-block-heading\"><strong><a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-9\/\">&lt;&lt;Lesson 9&gt;&gt;<\/a><a href=\"http:\/\/excelvbatutor.com\/index.php\/tutorial\/\"> [Contents]<\/a> <a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-11-date-time-functions\/\">&lt;&lt;Lesson 11&gt;&gt;<\/a><\/strong><\/h4>\n\n\n\n<p>Excel VBA offers a number of financial functions that can be used for accounting and financial calculations. In this lesson, we shall deal some of those functions<br>\nthat perform basic financial calculations. They are PV, FV and Pmt.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">10.1 PV<\/h3>\n\n\n\n<p>PV returns the present value of a certain amount of money a person needs to invest in order to earn a certain amount of money in the future(future value),<br>\nbased on the interest rate and the number of years this amount of money is kept. Additionally, it can also return the present value of an annuity which means the present value of a series of payments in<br>\nthe future<\/p>\n\n\n\n<p>The syntax of PV in Excel VBA is<\/p>\n\n\n\n<p>PV(Rate, Nper, Pmt, FV, Due)<\/p>\n\n\n\n<p>The parameters in the parentheses are explained below:<\/p>\n\n\n\n<p>Rate &#8211; Interest rate per period<br>\nNper &#8211; Number of payment periods<br>\nPmt &#8211; Amount of periodic payment for an annuity<br>\nFV &#8211; Future value<br>\nDue &#8211; Indicates when the payment is due. Its value is 1 for beginning of month and 0 for end of the month<\/p>\n\n\n\n<p><script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script><br>\n<ins class=\"adsbygoogle\" style=\"display: block; text-align: center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-3033628290023372\" data-ad-slot=\"9639157585\"><\/ins><br>\n<script><br \/>\n     (adsbygoogle = window.adsbygoogle || []).push({});<br \/>\n<\/script><\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Example 10.1<\/h4>\n\n\n\n<p>Do you know how much you need to invest today and how much you need to save monthly in order to obtain $1,000,000 thirty years from now?<br>\nLet&#8217;assume a fixed deposit interest rate is 4% per annum and you are willing to save $100 monthly in the bank, you can write the following Excel VBA code<br>\nto find out the initial investment you need to fork out.<\/p>\n\n\n\n<p>Private Sub CommandButton1_Click()<br>\nDim TheRate, FuVal, Payment As Single<\/p>\n\n\n\n<p>Dim NPeriod As Integer<\/p>\n\n\n\n<p>TheRate = InputBox(&#8220;Enter the rate per annum&#8221;)<br>\nFuVal = InputBox(&#8220;Enter future value&#8221;)<br>\nPayment = -InputBox(&#8220;Enter amount of monthly payment&#8221;)<br>\nNPeriod = InputBox(&#8220;Enter number of years&#8221;)<\/p>\n\n\n\n<p>MsgBox (&#8220;The Initial Investment is &#8221; &amp; Round(PV(TheRate \/ 12 \/ 100, NPeriod * 12, Payment, FuVal, 1), 2))<br>\nEnd Sub<\/p>\n\n\n\n<p>Running the program will produce a series of input boxes where the user can enter various values. The answer is shown in Figure 10.1.<br>\nThe value is negative because this is the amount you need to pay.\n<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/excelvbatutor.com\/vba_img\/figure_L10.1.jpg\" alt=\"\"\/><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">Figure 10.1<\/h4>\n\n\n\n<p><script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script><br>\n<ins class=\"adsbygoogle\" style=\"display: block; text-align: center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-3033628290023372\" data-ad-slot=\"9639157585\"><\/ins><br>\n<script><br \/>\n     (adsbygoogle = window.adsbygoogle || []).push({});<br \/>\n<\/script><\/p>\n\n\n\n<h4 class=\"wp-block-heading\">10.2 FV<\/h4>\n\n\n\n<p>FV returns the amount of money you will earn in future by putting in an initital investment and continue to pay certain amount periodically.<br>\nThe amount is depending on the interest rate and the duration. It reflects time value of money.<\/p>\n\n\n\n<p>The syntax of FV in Excel VBA is<\/p>\n\n\n\n<p>FV(Rate, Nper, Pmt, PV, Due)<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Example 10.2<\/h4>\n\n\n\n<p>In this example, you want to find the future value if your initial investment is $100,000, your monthly payment is $100, interest rate 5% and the investment period is 30 years<\/p>\n\n\n\n<p>Private Sub CommandButton1_Click()<\/p>\n\n\n\n<p>Dim TheRate, PVal, Payment As Single<\/p>\n\n\n\n<p>Dim NPeriod As Integer<\/p>\n\n\n\n<p>TheRate = InputBox(&#8220;Enter the rate per annum&#8221;)<br>\nPVal = InputBox(&#8220;Enter initial investment amount)<br>\nPayment = -InputBox(&#8220;Enter amount of monthly payment&#8221;)<br>\nNPeriod = InputBox(&#8220;Enter number of years&#8221;)<\/p>\n\n\n\n<p>MsgBox (&#8220;The Initial Investment is &#8221; &amp; Round(FV(TheRate \/ 12 \/ 100, NPeriod * 12, -Payment, -PVal, 0), 2))<\/p>\n\n\n\n<p>End Sub<\/p>\n\n\n\n<p>We place negative signs in front of Payment and Pval as you are paying out the money. Running the program will produce a series of input boxes where the user can enter various values. The answer is shown in Figure 10.2.<br>\n.\n<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/excelvbatutor.com\/vba_img\/figure_L10.2.jpg\" alt=\"\"\/><\/figure>\n\n\n\n<h5 class=\"wp-block-heading\">Figure 10.2<\/h5>\n\n\n\n<h3 class=\"wp-block-heading\">10.3 Pmt<\/h3>\n\n\n\n<p>Pmt is an Excel VBA function that returns a number of periodic payments you need to make for a certain PV and FV.<\/p>\n\n\n\n<p>The syntax of Pmt in Excel VBA is<\/p>\n\n\n\n<p>Pmt(Rate,Nper, PV, FV, Due)<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Example 10.3<\/h4>\n\n\n\n<p>For example, you borrowed a mortgage loan of 500,000 from the bank to buy a property. You have agreed to pay back the loan in thirty years<br>\nby a monthly instalment method at an interest rate of 4% per annum. Now you need to calculate the amount of monthly payment.<br>\nIn this case, Rate=4\/100\/12 (monthly rate), Nper=30&#215;12=360 months,PV=500,000, FV=0 (loan settled) and due=0 as you normally paying at end of the month.<\/p>\n\n\n\n<p>Private Sub CommandButton1_Click()<\/p>\n\n\n\n<p>Dim TheRate, PVal As Single<\/p>\n\n\n\n<p>Dim NPeriod As Integer<\/p>\n\n\n\n<p>TheRate = InputBox(&#8220;Enter the rate per annum&#8221;)<br>\nPVal = InputBox(&#8220;Enter Loan Amount&#8221;)<br>\nNPeriod = InputBox(&#8220;Enter number of years&#8221;)<\/p>\n\n\n\n<p>MsgBox (&#8220;The monthly payment is &#8221; &amp; Round(Pmt(TheRate \/ 12 \/ 100, NPeriod * 12, Pval, 0, 0), 2))<\/p>\n\n\n\n<p>End Sub<\/p>\n\n\n\n<p>Running the program will produce a series of input boxes where the user can enter various values. The answer is shown in Figure 10.3.<br>\n.The value is negative because this is the amount you need to pay\n<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/excelvbatutor.com\/vba_img\/figure_L10.3.jpg\" alt=\"\"\/><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">Figure 10.3<\/h4>\n\n\n\n<h4 class=\"wp-block-heading\"><strong><a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-9\/\">&lt;&lt;Lesson 9&gt;&gt;<\/a><a href=\"http:\/\/excelvbatutor.com\/index.php\/tutorial\/\"> [Contents]<\/a> <a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-11-date-time-functions\/\">&lt;&lt;Lesson 11&gt;&gt;<\/a><\/strong><\/h4>\n","protected":false},"excerpt":{"rendered":"<p>&lt;&lt;Lesson 9&gt;&gt; [Contents] &lt;&lt;Lesson 11&gt;&gt; Excel VBA offers a number of financial functions that can be used for accounting and financial calculations. In this lesson, we shall deal some of those functions that perform basic financial calculations. They are PV, FV and Pmt. 10.1 PV PV returns the present value of a certain amount of &hellip; <a href=\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-10-financial-functions\/\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> &#8220;Excel VBA Lesson 10: Financial Functions&#8221;<\/span><\/a><\/p>\n","protected":false},"author":5012,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"categories":[14],"tags":[],"class_list":["post-2890","page","type-page","status-publish","hentry","category-function"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.1.1 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Excel VBA Lesson 10: Financial Functions - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/excelvbatutor.com\/vba_lesson10.htm\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Excel VBA Lesson 10: Financial Functions - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor\" \/>\n<meta property=\"og:description\" content=\"&lt;&lt;Lesson 9&gt;&gt; [Contents] &lt;&lt;Lesson 11&gt;&gt; Excel VBA offers a number of financial functions that can be used for accounting and financial calculations. In this lesson, we shall deal some of those functions that perform basic financial calculations. They are PV, FV and Pmt. 10.1 PV PV returns the present value of a certain amount of &hellip; Continue reading &quot;Excel VBA Lesson 10: Financial Functions&quot;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/excelvbatutor.com\/vba_lesson10.htm\" \/>\n<meta property=\"og:site_name\" content=\"Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor\" \/>\n<meta property=\"article:modified_time\" content=\"2020-04-23T10:51:29+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/excelvbatutor.com\/vba_img\/figure_L10.1.jpg\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-10-financial-functions\/\",\"url\":\"https:\/\/excelvbatutor.com\/vba_lesson10.htm\",\"name\":\"Excel VBA Lesson 10: Financial Functions - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor\",\"isPartOf\":{\"@id\":\"https:\/\/excelvbatutor.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/excelvbatutor.com\/vba_lesson10.htm#primaryimage\"},\"image\":{\"@id\":\"https:\/\/excelvbatutor.com\/vba_lesson10.htm#primaryimage\"},\"thumbnailUrl\":\"https:\/\/excelvbatutor.com\/vba_img\/figure_L10.1.jpg\",\"datePublished\":\"2017-10-11T01:02:03+00:00\",\"dateModified\":\"2020-04-23T10:51:29+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/excelvbatutor.com\/vba_lesson10.htm#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/excelvbatutor.com\/vba_lesson10.htm\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/excelvbatutor.com\/vba_lesson10.htm#primaryimage\",\"url\":\"https:\/\/excelvbatutor.com\/vba_img\/figure_L10.1.jpg\",\"contentUrl\":\"https:\/\/excelvbatutor.com\/vba_img\/figure_L10.1.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/excelvbatutor.com\/vba_lesson10.htm#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/excelvbatutor.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel VBA Lesson 10: Financial Functions\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/excelvbatutor.com\/#website\",\"url\":\"https:\/\/excelvbatutor.com\/\",\"name\":\"Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor\",\"description\":\"Master Excel VBA with free tutorials, examples, and personalized guidance. Perfect for beginners and advanced users looking to automate Excel.\",\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Excel VBA Lesson 10: Financial Functions - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/excelvbatutor.com\/vba_lesson10.htm","og_locale":"en_US","og_type":"article","og_title":"Excel VBA Lesson 10: Financial Functions - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","og_description":"&lt;&lt;Lesson 9&gt;&gt; [Contents] &lt;&lt;Lesson 11&gt;&gt; Excel VBA offers a number of financial functions that can be used for accounting and financial calculations. In this lesson, we shall deal some of those functions that perform basic financial calculations. They are PV, FV and Pmt. 10.1 PV PV returns the present value of a certain amount of &hellip; Continue reading \"Excel VBA Lesson 10: Financial Functions\"","og_url":"https:\/\/excelvbatutor.com\/vba_lesson10.htm","og_site_name":"Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","article_modified_time":"2020-04-23T10:51:29+00:00","og_image":[{"url":"https:\/\/excelvbatutor.com\/vba_img\/figure_L10.1.jpg","type":"","width":"","height":""}],"twitter_misc":{"Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-10-financial-functions\/","url":"https:\/\/excelvbatutor.com\/vba_lesson10.htm","name":"Excel VBA Lesson 10: Financial Functions - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","isPartOf":{"@id":"https:\/\/excelvbatutor.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/excelvbatutor.com\/vba_lesson10.htm#primaryimage"},"image":{"@id":"https:\/\/excelvbatutor.com\/vba_lesson10.htm#primaryimage"},"thumbnailUrl":"https:\/\/excelvbatutor.com\/vba_img\/figure_L10.1.jpg","datePublished":"2017-10-11T01:02:03+00:00","dateModified":"2020-04-23T10:51:29+00:00","breadcrumb":{"@id":"https:\/\/excelvbatutor.com\/vba_lesson10.htm#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/excelvbatutor.com\/vba_lesson10.htm"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/excelvbatutor.com\/vba_lesson10.htm#primaryimage","url":"https:\/\/excelvbatutor.com\/vba_img\/figure_L10.1.jpg","contentUrl":"https:\/\/excelvbatutor.com\/vba_img\/figure_L10.1.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/excelvbatutor.com\/vba_lesson10.htm#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/excelvbatutor.com\/"},{"@type":"ListItem","position":2,"name":"Excel VBA Lesson 10: Financial Functions"}]},{"@type":"WebSite","@id":"https:\/\/excelvbatutor.com\/#website","url":"https:\/\/excelvbatutor.com\/","name":"Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","description":"Master Excel VBA with free tutorials, examples, and personalized guidance. Perfect for beginners and advanced users looking to automate Excel.","inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/pages\/2890","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/users\/5012"}],"replies":[{"embeddable":true,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/comments?post=2890"}],"version-history":[{"count":7,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/pages\/2890\/revisions"}],"predecessor-version":[{"id":3344,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/pages\/2890\/revisions\/3344"}],"wp:attachment":[{"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/media?parent=2890"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/categories?post=2890"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/tags?post=2890"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}