1 / 39

Creating Custom Excel Functions with Visual Basic for Advanced Calculations

This guide provides step-by-step instructions on how to create custom functions in Excel using Visual Basic for Applications (VBA). It covers saving workbooks as macro-enabled files, accessing the Developer menu, and defining functions such as `RectangleArea`, which calculates the area based on height and width. Additionally, it demonstrates how to calculate futures prices using the formula `Future(Spot, Rf, Time)` and how to implement the Dividend Growth Model. Perfect for traders and analysts looking to enhance their Excel skills with custom calculations.

jeremy-butler
Download Presentation

Creating Custom Excel Functions with Visual Basic for Advanced Calculations

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 10. Visual BasicFunctions

  2. Open Excel

  3. Click File -> Save As

  4. Save As a Excel Macro-Enabled Workbook

  5. Click File -> Options

  6. Click Customize Ribbon

  7. Tick Developer

  8. Click OK

  9. Select Developer menu

  10. Functions • Excel uses functions to do most calculations =SUM(x) =COUNTIF(x,y) =VLOOKUP(x,y,z) • They take an input value e.g. x, y, z then perform a calculation and return a result • We can write our own functions in Visual Basic

  11. Area of a rectangle • We want to be able to type in a function such as =RectangleArea(Height, Width) • Excel should do the following calculation Area = Height * Width • Excel should then give us the result

  12. Click on Visual Basic

  13. Click Insert -> Module

  14. Type Function RectangleArea(Height, Width)

  15. Press Enter on Keyboard and it will addEnd Function

  16. This means Excel asks you for Height and WidthIt will then do calculations with these variables

  17. Type RectangleArea = Height * Width

  18. Click Save

  19. Close Visual Basic editor

  20. Click Formulas -> Insert Function

  21. Select Category All

  22. Scroll down to RectangleArea

  23. Click OK

  24. You are asked for Height and Weight

  25. Type in Height 3 and Width 2

  26. Click OK

  27. The result of the function is displayed in the cell

  28. You can also use this function by typing into the cell directly

  29. Click cell B1

  30. Type =RectangleArea(5,3) and press Enter

  31. Calculates the result for you

  32. Pricing a Futures Contract • Traders may want to calculate futures prices • We want Excel to calculate the Futures price by providing the Spot price, Risk-Free rate and Time =Future(Spot, Rf, Time) • Excel should calculate the following formula Future = (Spot)e(Rf)(Time)

  33. Click Visual Basic

  34. Type Function Future(Spot, Rf, Time) Future = Spot * exp(Rf * Time)

  35. Save and Close

  36. Type in the following input values

  37. In cell D10 type =Future(D6,D7,D8) and press Enter

  38. Calculates Futures Price

  39. Challenge • Create a function which will calculate the price of a share using the Dividend Growth model • You want a user to be able to use a function where they get the price by typing in a function such as: =DGMPrice (Dividend, InterestRate, GrowthRate) • Excel should then do the following calculation and return the result DGMPrice = Dividend / (InterestRate – GrowthRate) • What is the fair price of a share if it has a dividend of £2, interest rate of 0.06, and growth rate of 0.02?

More Related