Trending February 2024 # Excel Vba Instr Function – Explained With Examples # Suggested March 2024 # Top 7 Popular

You are reading the article Excel Vba Instr Function – Explained With Examples updated in February 2024 on the website Flu.edu.vn. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested March 2024 Excel Vba Instr Function – Explained With Examples

Yesterday, I got an email from one of my readers – June.

She wanted to know how to apply bold font format to a specific part of a string within a cell. For example, apply the bold format to only the word ‘Hello’ from ‘Hello World’.

And she wanted to do this for hundreds of cell at once.

Since there is no inbuilt functionality in Excel that can do that, I created a simple macro that uses the Excel VBA InStr function (you will see how to do this in Example 4 in this tutorial).

But first, let’s see how the Excel VBA InStr function works!

In this tutorial, I will explain the usage of InStr function in Excel VBA and see some practical examples where it can be used.

InStr function finds the position of a specified substring within the string and returns the first position of its occurrence.

For example, if you want to find the position of ‘x’ in ‘Excel’, using the Excel VBA InStr function would return 2.

InStr(

[Start]

,

String1

,

String2

,

[Compare]

)

[Start] – (optional argument) this is an integer value that tells the InStr function the starting position from which it should start looking. For example, if I want the search to start from the beginning, I will enter the value as 1. If I want it to begin with the third character onwards, I will use 3. If omitted, the default value of 1 is taken.

String1 – This is the main string (or the parent string) in which you want to search. For example, if you’re looking for the position of x in Excel, String 1 would be “Excel”.

String2 – This is the substring that you are searching for. For example, if you’re looking for the position of x in Excel, String2 would be x.

[Compare] – (optional argument) You can specify one the following three values for [compare] argument:

vbBinaryCompare – This would do a character by character comparison. For example, if you’re looking for ‘x’ in ‘Excel’, it will return 2, but if you’re looking for ‘X’ in ‘Excel’, it will return 0 as X is in upper case. You can also use 0 instead of vbBinaryCompare. If the [Compare] argument is omitted, this is the taken as default.

vbTextCompare – This would do a textual comparison. For example, if you look for ‘x’ or ‘X’ in Excel, it would return 2 in both the cases. This argument ignores the letter case. You can also use 1 instead of vbTextCompare.

vbDatabaseCompare –  This is used for Microsoft Access only.  It uses the information in the database to perform the comparison. You can also use 2 instead of vbDatabaseCompare.

InStr is a VBA function and not a worksheet function. This means that you can not use it within the worksheet.

If String2 (which is the substring whose position you’re looking for) is empty, the function would return the value of the [Start] argument.

If the InStr function can not find the substring within the main string, it would return 0.

Now let’s have a look at some example of using the Excel VBA InStr Function

In this example, I will use the InStr function to find the position of ‘V’ in ‘Excel VBA’ from the beginning.

The code for this would be:

Sub FindFromBeginning() Dim Position As Integer Position = InStr(1, "Excel VBA", "V", vbBinaryCompare) MsgBox Position End Sub

When you run this code, it will show a message box with the value 7, which is the position of ‘V’ in the string ‘Excel VBA’.

Suppose, I want to find the position of ‘the’ in the sentence – ‘The quick brown fox jumps over the lazy dog’

However, I want the search to begin with the second word onwards.

In this case, we need to change the [Start] argument to make sure it specifies the position from where the second word starts.

Here is the code that will do this:

Sub FindFromSecondWord() Dim Position As Integer Position = InStr(4, "The quick brown fox jumps over the lazy dog", "the", vbBinaryCompare) MsgBox Position End Sub

This code will show the message box with the value 32 as we have specified the starting position as 4. Hence it ignores the first ‘The’ and finds the second ‘the’ in the sentence.

If you want to make it more dynamic, you can enhance the code so that it automatically ignore the first word.

Here is the enhanced code that will do this:

Sub FindFromSecondWord() Dim StartingPosition As Integer Dim Position As Integer StartingPosition = InStr(1, "The quick brown fox jumps over the lazy dog", " ", vbBinaryCompare) Position = InStr(StartingPosition, "The quick brown fox jumps over the lazy dog", "the", vbBinaryCompare) MsgBox Position End Sub

This code first finds the position of a space character and stores it in the variable StartingPosition.

It then uses this variable as the starting position to look for the word ‘the’.

Hence it returns 32 (which is the starting position of ‘the’ after the first word).

You can easily create a custom function to find the position of @ in an email address using the Excel VBA InStr function.

Here is the code to create the custom function:

Function FindPosition(Ref As Range) As Integer Dim Position As Integer Position = InStr(1, Ref, "@") FindPosition = Position End Function

Now you can use this custom function as any other worksheet function. It will take a cell reference as input and give you the position of @ in it.

Similarly, you can create a custom function to find the position of any substring within the main string.

This is the query that was asked by June (my reader who also inspired me to write this tutorial).

Here is a sample data in the format June sent me:

Her query was to make the numbers outside the bracket bold.

Here is the code I created that does this:

Sub Bold() Dim rCell As Range Dim Char As Integer For Each rCell In Selection CharCount = Len(rCell) Char = InStr(1, rCell, "(") rCell.Characters(1, Char - 1).Font.Bold = True Next rCell End Sub

The above code uses the For Each loop to go through each of the cells in the selection. It identifies the position of the opening bracket character using the InStr function. It then changes the font of the text before the bracket.

To use this code, you need to copy and paste in a module in the VB editor.

Once you have copy pasted the code, select the cells in which you want to do this formatting and run the macro (as shown below).

You May Also Like the following Excel VBA Tutorials:

You're reading Excel Vba Instr Function – Explained With Examples

How To Use Excel Vba Mid With Examples?

MID Function in Excel VBA

MID Function is commonly used to extract a substring from a full-text string. It is categorized under String type variable. VBA Mid function allows you to extract the middle part of the string from a full-text string. VBA string functions do not change the original string. Usually, they will return a new string based on the function you input in the code. VBA Mid function returns a substring from within a supplied text or a string.

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax of VBA MID in Excel

The syntax for VBA Mid function in excel is as follows:

Mid(string_to_search, starting position, number_of_characters)

Start_number or Starting point: Character position or number from where to start extracting the sub-string.

Note: If you enter a greater than the number of characters in a string, then it will return an empty string (“”)

char_numbers or Length (Optional): Number of characters to extract or to return from the start position.

How to Use Excel VBA MID?

Below are the different examples to use Mid in Excel using VBA code.

You can download this VBA MID Excel Template here – VBA MID Excel Template

Excel VBA MID – Example #1

Follow the below steps to use excel VBA MID.

Excel VBA MID – Example #2

Now the blank module is created, it is also called as a code window, where you can start writing VBA MID function statement codes.

Suppose, I have the word “[email protected]” and you want to extract email domain i.e. “OUTLOOK” from this sentence with the help of VB MID function macro code.

Step 1: In the VBA editor, I have a given a name as VBA_MID_1() after typing Sub.

Code:

Sub

VBA_MID_1()

End Sub

Step 2: As the MID function is categorized under string type variables, DIM (Dimension) is used in a VBA code to declare a variable name, it’s type.

Code:

Sub

VBA_MID_1()

Dim

MiddleValue

As String

End Sub

Code:

Sub

VBA_MID_1()

Dim

MiddleValue

As String

MiddleValue = Mid

End Sub

Step 4: First parameter or argument is String i.e. It is a text String from where you want to start extracting the specified number of characters. Here it is “[email protected]” when using strings argument, you have to surround the text in quotation mark.

Start As Long: It is the starting position of the character from where you want to extract. Here, in this case, it is “10”

Length: It is a number of characters to extract or to return from the start position, Here, in this case, it is “7”

Code:

Sub

VBA_MID_1()

Dim

MiddleValue

As String

MiddleValue = Mid("[email protected]", 10, 7) MsgBox (MiddleValue)

End Sub

We have completed the MID function arguments here. Now, I want to display this result of the variable in the message box. Let’s press Ctrl + Space, type Msg, in the bracket you can mention a variable name.

Excel VBA MID – Example #3

Instead of output appearing in the message box, I want the result or output data to appear in the worksheet, i.e. in cell “D5”. In the worksheet, I have a data, i.e. Email id of the employee in cell “C5”, now I want to extract email domain form this, I can use the MID function with a slight modification of code.

Step 1: After declaring a variable, I need to input the variable name again and cell address of the full-text string with the help of Range or cell function.

Code:

Sub

VBA_MID_2()

Dim

MiddleValue

As String

MiddleValue = Range("C5")

End Sub

Step 2: Now, again I need to enter the variable name, and apply mid function & input its arguments.

Code:

Sub

VBA_MID_2()

Dim

MiddleValue

As String

MiddleValue = Range("C5") MiddleValue = Mid("[email protected]", 10, 7)

End Sub

In the previous example, we entered a msg box for the result to be displayed, now, I want the result to appear in a specific cell in a worksheet. For this, I  need to enter the range or cell function for the result to be displayed.

Step 3: Let’s apply range function, initially we need to input the range or cell function and Later enter the variable name, so that in that specific cell (“D5”), the result appears.

Code:

Sub

VBA_MID_2()

Dim

MiddleValue

As String

MiddleValue = Range("C5") MiddleValue = Mid("[email protected]", 10, 7) Range("D5") = MiddleValue

End Sub

Things to Remember

In Excel VBA MID function, Length argument is an optional parameter. If you fail to enter any argument or ignore this, VBA Mid function returns all characters from the supplied start position to the end of the string.

In Excel VBA MID function, if the start number argument is greater than the length of the text string, then the MID function returns an empty string (zero-length).

The mid function is very significant and useful along with loops function, as it helps you to examine one character at a time from a string of text.

Recommended Articles

This is a guide to VBA MID. Here we discuss how to use MID in Excel using VBA code along with few practical examples and downloadable excel template. You can also go through our other suggested articles –

How To Use Vba Isnull Function In Excel?

VBA ISNULL Function

ISNULL function in VBA is used for finding the null value in excel. This seems easy but when we have a huge database which is connected to multiple files and sources and if we asked to find the Null in that, then any manual method will not work. For that, we have a function called IsNull in VBA which finds the Null value in any type of database or table. This can only be done in VBA, we do not have any such function in Excel.

Syntax of ISNULL in Excel VBA

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

The syntax for the VBA ISNULL function in excel is as follows:

As we can see in the above screenshot, IsNull uses only one expression and to as Boolean. Which means it will give the answer as TRUE and FALSE values. If the data is Null then we will get TRUE or else we will get FALSE as output.

How to Use VBA ISNULL Function in Excel?

You can download this VBA ISNULL Excel Template here – VBA ISNULL Excel Template

Example #1 – VBA ISNULL

Follow the below steps to use IsNull in Excel VBA.

Step 1: To apply VBA IsNull, we need a module. For this go to the VBA window and under the Insert menu select Module as shown below.

Step 2: Once we do that we will get a blank window of fresh Module. In that, write the subcategory of VBA IsNull or in any other name as per your need.

Code:

Sub

VBA_IsNull()

End Sub

Step 3: For IsNull function, we will need one as a Variant. Where we can store any kind of value. Let’s have a first variable Test as Variant as shown below.

Code:

Sub

VBA_IsNull()

Dim

Test

As Variant

End Sub

Step 4: As we know that IsNull works on Boolean. So we will need another variable. Let’s have our second variable Answer as Boolean as shown below. This will help us in knowing whether IsNull is TRUE or FALSE.

Code:

Sub

VBA_IsNull()

Dim

Test

As Variant

Dim

Answer

As Boolean

End Sub

Step 5: Now give any value to the first variable Test. Let’s give it a text value “VBA Macro” as shown below.

Code:

Sub

VBA_IsNull()

Dim

Test

As Variant

Dim

Answer

As Boolean

Test = "VBA Macro"

End Sub

Step 6: Now we will use our second variable Answer with IsNull function as shown below.

Code:

Sub

VBA_IsNull()

Dim

Test

As Variant

Dim

Answer

As Boolean

Test = "VBA Macro" Answer = IsNull(

End Sub

As we have seen in the explanation of VBA IsNull, that syntax of IsNull is Expression only. And this Expression can be a text, cell reference, direct value by entering manually or any other variable assigned to it.

Code:

Sub

VBA_IsNull()

Dim

Test

As Variant

Dim

Answer

As Boolean

Test = "VBA Macro" Answer = IsNull(Test)

End Sub

Step 8: Once done, then we will need a message box to print the value of IsNull if it is TRUE or FALSE. Insert Msgbox and give any statement which we want to see. Here we have considered “Is the Test is null?” as shown below.

Code:

Sub

VBA_IsNull()

Dim

Test

As Variant

Dim

Answer

As Boolean

Test = "VBA Macro" Answer = IsNull(Test) MsgBox "Is the Test is null? : "

End Sub

Step 9: And then add rest of the variable which we defined above separated by the ampersand (&) as shown below which includes our second variable Answer and name of the message box as “VBA ISNULL Function Example”.

Code:

Sub

VBA_IsNull()

Dim

Test

As Variant

Dim

Answer

As Boolean

Test = "VBA Macro" Answer = IsNull(Test) MsgBox "Is the Test is null? : " & Answer, vbInformation, "VBA ISNULL Function Example"

End Sub

Step 10: Now compile the code by pressing F8 and run it by pressing the F5 key if there is no error found. We will see the Isnull function has returned the Answer as FALSE. Which means text “VBA Macro” is not null.

Step 11: Now we will see if numbers can be null or not. For this, we use a new module or we can use the same code that we have written above. In that, we just need to make changes. Assign any number to Test variable in place of text “VBA Macro”. Let’s consider that number as 123123 as shown below.

Code:

Sub

VBA_IsNull()

Dim

Test

As Variant

Dim

Answer

As Boolean

Test = 123123 Answer = IsNull(Test) MsgBox "Is the Test is null? : " & Answer, vbInformation, "VBA ISNULL Function Example"

End Sub

Step 12: Now again compile the code or we can compile the current step only by putting the cursor there and pressing F8 key. And run it. We will get the message box with the statement that our Test variable which is number 123123 is also not a Null. It is FALSE to call it a null.

Step 13: Now it is clear that neither Text nor Number can be Null. To test further, now we will consider a blank. A reference which has no value. For this, in the same previously written code put the double inverted (“”) commas with nothing in it in Test variable as shown below. Now we will is if a Blank can be a null or not.

Code:

Sub

VBA_IsNull()

Dim

Test

As Variant

Dim

Answer

As Boolean

Test = "" Answer = IsNull(Test) MsgBox "Is the Test is null? : " & Answer, vbInformation, "VBA ISNULL Function Example"

End Sub

Step 14: We will get a message which says the Blank reference is also not null. It is FALSE to call it so.

Step 15: We have tried Text, Number and Blank for testing if they are null or not. Now we will text Null itself under variable Test as see if this is a null or not.

Code:

Sub

VBA_IsNull()

Dim

Test

As Variant

Dim

Answer

As Boolean

Test = Null Answer = IsNull(Test) MsgBox "Is the Test is null? : " & Answer, vbInformation, "VBA ISNULL Function Example"

End Sub

Step 16: Now run the code. We will in the message box the statement for “Is the Test is null?” has come TRUE.

Which means, in data if there are any cells with Blank, Space, Text or Number. Those cells will not be considered as Null.

Pros of VBA IsNull

We can find if a cell is Null or not.

We can test any variable if it is null or not.

This quite helps in a big database which is fetched from some source.

Things to Remember

IsNull finds only Null as Null. Text, Numbers, and Blanks are not null.

IsNull is only applicable in VBA. Excel doesn’t have any function as IsNull or other matching function which can give the same result as IsNull.

To use the code multiple times, it is better to save the excel in Macro Enable Excel format. This process helps in retaining the code for future use.

IsNull only returns the value in the Boolean form, means in TRUE and FALSE

Considering the Variant as Test variable allow us to use numbers, words and blank values in it. It considers all the type of values majorly used for Boolean.

Recommended Articles

This is a guide to VBA ISNULL. Here we discuss how to use Excel VBA ISNULL function along with practical examples and downloadable excel template. You can also go through our other suggested articles –

Top 4 Vba Macro Excel Examples For Beginners

Excel VBA Examples

This is very thing when I started working and learning VBA Macros, I was confused where to start. We have already seen VBA Editor which has all the definitions of VBA Editor Window options and tabs. As a beginner, we always do not know the way to start the thing until we find one. This article is the solution for those who face difficulty in using and learning VBA Examples and creating Macros. We all have faced the time when certain things in Excel could have been automated using Macro. But, someone who doesn’t know how to use VBA will not able to create one. This will be a useful guide for those who are new to VBA Macro coding.

Examples of VBA in Excel for Beginners

Below are the examples of VBA in Excel:

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

You can download this VBA Examples Excel Template here – VBA Examples Excel Template

VBA Example #1

Let’s see a simple example of using VBA Coding. For this, follow the below steps:

Step 1: Open a VBA Module where we will be writing our code from Insert menu tab as shown below.

Step 2: Now write the subprocedure of VBA macro in any name.

Code:

Sub

VBA_Examples1()

End Sub

Step 3: Define a variable using DIM where we will be storing numbers as Integers.

Code:

Sub

VBA_Examples1()

Dim

A

As Integer

End Sub

Step 4: Now give any number to the above-defined variable, as shown below.

Code:

Sub

VBA_Examples1()

Dim

A

As Integer

A = 100

End Sub

Step 5: Now we will use the message box to print the value stored in Variable A.

Code:

Sub

VBA_Examples1()

Dim

A

As Integer

A = 100 MsgBox A

End Sub

Code:

Sub

VBA_Examples1()

Dim

A

As Integer

A = 100 MsgBox A

End Sub

Step 7: This could have been done using Debug Print function as well, whose values are seen in Immediate Window. (Note: Press Ctrl+ G to open immediate window)

Code:

Sub

VBA_Examples1()

Dim

A

As Integer

A = 100 Debug.Print A

End Sub

VBA Example #2

Now in this example, we will see another example where we will use the For-Next loop to print the sheet names. For this, follow the below steps:

Step 1: Open a Module and write the subprocedure as shown below. We can use any name to define this.

Code:

Sub

VBA_Examples2()

End Sub

Step 2: Choose a variable as an Integer. We can choose the name variable as we did in example-1.

Code:

Sub

VBA_Examples2()

Dim

A

As Integer

End Sub

Step 3: Open a For-Next loop as shown below.

Code:

Sub

VBA_Examples2()

Dim

A

As Integer

For

Next

A

End Sub

Step 4: Select the position of cell from where we want to see the Sheet names. Here as we don’t have any header reference so we can start from cell 1.

Code:

Sub

VBA_Examples2()

Dim

A

As Integer

For

A = 1

To

Sheets.Count

Next

A

End Sub

Step 5: Now we assign the variable A with cell Value to see the Sheet name as shown below.

Code:

Sub

VBA_Examples2()

Dim

A

As Integer

For

A = 1

To

Sheets.Count Cells(A, 1).Value = Sheets(A).Name

Next

A

End Sub

We will see, as we had 3 sheets, all named default so their name got printed from cell A1 to below till the number of Sheets we have.

VBA Example #3

In this example, we will learn how to print the numbers. This process is quite the same as we have seen in example-2. For this, follow the below steps:

Step 1: Open a Module and write the subprocedure.

Code:

Sub

VBA_Examples3()

End Sub

Step 2: Consider a variable using DIM as Integer. As we are using the numbers to Integer data type should be used.

Code:

Sub

VBA_Examples3()

Dim

A

As Integer

End Sub

Step 3: Again open a For-Next loop as shown below.

Code:

Sub

VBA_Examples3()

Dim

A

As Integer

For

Next

A

End Sub

Step 4: Now select the cell range from where we want to see number till the last in For syntax.

Code:

Sub

VBA_Examples3()

Dim

A

As Integer

For

A = 1

To

10

Next

A

End Sub

Step 5: Now fix the cell position with variable A. Here, second position vertex in Cell shows Column number. As we chose 1 so we expect to see the numbers starting from cell A1.

Code:

Sub

VBA_Examples3()

Dim

A

As Integer

For

A = 1

To

10 Cells(A, 1).Value = A

Next

A

End Sub

Step 7: What if we try to add these numbers in the second column? Change the column vertex from 1 to 2. Or else add another line of code as shown below.

Code:

Sub

VBA_Examples3()

Dim

A

As Integer

For

A = 1

To

10 Cells(A, 1).Value = A Cells(A, 2).Value = A

Next

A

End Sub

VBA Example #4

In this example, we will see how to change the color of blank cells from the selected ranges. For this, we have used the same number as we did in the above example but we have deleted some of the cells to get the output.

Step 1: Open a Module, write the subprocedure as shown below.

Code:

Sub

VBA_Example4()

End Sub

Step 2: Define a variable using DIM as Range, as we are selecting the exiting number range.

Code:

Sub

VBA_Example4()

Dim

A

As Range

End Sub

Step 3: Now set this variable as Selection

Code:

Sub

VBA_Example4()

Dim

A

As Range

Set

A = Selection

End Sub

Step 4: Now select the Range of numbers using defined variable A and choose xlCellTypeBlanks to select the blank cells only.

Code:

Sub

VBA_Example4()

Dim

A

As Range

Set

A = Selection A.Cells.SpecialCells(xlCellTypeBlanks).

End Sub

Step 5: Now to change the interior color of blank cells, use Interior.Color and choose the color by which we want to highlight the blank cells. Use vbBlue or vbGreen type to define the color. The use of vb is a must.

Code:

Sub

VBA_Example4()

Dim

A

As Range

Set

A = Selection A.Cells.SpecialCells(xlCellTypeBlanks).Interior.Color = vbBlue

End Sub

Pros and Cons of Excel VBA Examples

VBA increases the efficiency of work.

By this, we want to reduce the repetitive tasks in one go.

We can create any type of Macro we want.

We can also record a Macro if doing the coding is not easy.

People who do not have coding knowledge or idea may find difficult using VBA Macro.

Things to Remember

Beginner in VBA should keep in mind to save the code in Macro enabled excel format. This will allow us to retain the code in the same file.

Always compile the complete code, even if the code is of one line. This will reduce our time in debugging if you face any error while after code is run.

Always start the learning process of VBA or any other coding language with small and easy code. This will give a better understanding and confidence.

Recommended Articles

This is a guide to VBA Examples in Excel. Here we discuss some useful examples of VBA Macro code in Excel along with downloadable excel template. You can also go through our other suggested articles –

Calculation (Examples With Excel Template)

Annuity Formula (Table of Contents)

Start Your Free Investment Banking Course

Download Corporate Valuation, Investment Banking, Accounting, CFA Calculator & others

What is the Annuity Formula?

Generally, insurance companies sell these annuity contracts. Insurance companies take those deposit amount and take the risk to guarantee regular future payments to investors. The annuity also gives investors the flexibility of making payments and that can be done in lump sum amount, monthly, quarterly, etc.

Formula for Annuity is as follow:

There are many ways in which we can define the annuity formula and it depends what we want to calculate.

If we want to see what is the lump sum amount which we have to pay today so that we can have stable cash flow in the future, we use the below formula:

P = C * [(1 – (1 + r)-n) / r]

Where,

P – Present value of Annuity or the lump sum amount

C – Future cash flow stream

r – Interest rate

n – Number of Periods

Similarly, if you want to find out what will be the cash flow stream, we can use the slightly modified formula:

C = P * r / [(1 – (1 + r)-n)]

Examples of Annuity Formula (With Excel Template)

Let’s take an example to understand the calculation of the Annuity in a better manner.

You can download this Annuity Formula Excel Template here – Annuity Formula Excel Template

Annuity Formula – Example #1

Let say you want to have $2000 payment of annuity from next year for 10 years. The current market rate is 10%. Let’s calculate how much you have to deposit today:

Solution:

Present Value of Annuity is calculated using the formula given below

P = C * [(1 – (1 + r)-n) / r]

Present Value of Annuity = $2000 * ((1 – (1 + 10%)-10) / 10%)

Present Value of Annuity = $12,289.13

Annuity Formula – Example #2

Let say your age is 30 years and you want to get retired at the age of 50 years and you expect that you will live for another 25 years. You have 20 years of service left and you want that when you retire, you will get an annual payment of $10,000 till you die (i.e. for 25 years after retirement). For that, we want to save money today. Market interest rate is 10%. You have $15000 which you can invest today. We will check that will that be enough to meet the targets.

Solution:

Now we want to get $10,000 starting from year 51 to year 75 (25 years).

Present Value of Annuity is calculated using the formula given below

P = C * [(1 – (1 + r)-n) / r]

Present Value of Annuity at Year 50 = $10,000 * ((1 – (1 + 10%)-25) / 10%)

Present Value of Annuity at Year 50 = $90,770.40

But that value you need at year 50 i.e. 20 years from now. You want to see the money you need today. So we need to calculate the present value of that amount today.

Present Value of Annuity = $90,770.40 / (1 + 10%)20

Present Value of Annuity = $13,492.44

Since you have $15,000 with you and you only need $13,492.44, you are covered and will be able to achieve your target.

Explanation

There are basically 2 types of annuities we have in the market:

Fixed Annuity: It is the traditional financial instrument which we discussed above. You invest a specific amount and the institution will guarantees you fixed periodic payments.

Variable Annuity: It is very different than the traditional fixed annuity. In this model, it does not guarantee you fixed payments, rather pays you based on the performance of the investments. So if an investment does well, you can have higher returns and vice versa.

Annuities, as we discussed above, provide a fixed series of payments once you pay the amount to the financial institutes. But how institutes able to pay the investor the fixed amount on a periodic basis is that they invest that amount in the financial instruments which are high in quality and provide fixed-income to the institutes. These instruments are generally high rated bonds and T-bills.

Relevance and Uses of Annuity Formula

Annuities are a great financial instrument for the investors who want to secure their future and want to have constant income coming in once they retire. Although annuity is a secure stream of payment which one gets to buy this financial instrument is not relevant for everyone. If you have enough income and not bothered that you will be short of money in the future, an annuity is not meant for you. You can choose other lucrative investments.

Also, there are some risks associated with an annuity which investors should also keep in mind. First is the opportunity cost. In an annuity, the market rates get locked and if the rate increase in the future, you will lose out those opportunities. But this can be mitigated up to an extent by not entering into long term annuity and doing gradual annuity. It will give you more room to play and make use of an increasing interest rate.

Annuity Formula Calculator

You can use the following Annuity Calculator

C r n P   P = C X 1 – (1 + r)-n r

0

X 1 – (1 +

0

)-

0

=

0

0

Recommended Articles

This is a guide to Annuity Formula. Here we discuss how to calculate Annuity along with practical examples. We also provide an Annuity calculator with a downloadable excel template. You may also look at the following articles to learn more –

Learn The Numpy Newaxis Function With Examples

Introduction to NumPy Newaxis

Numpy newaxis is one of the various functions supported by python numpy library that allows us to change or expand the dimension of a numpy array in the position in which it has been entered. When we include each of the newaxis objects in the tuple of our variable or array we will get a new additional dimension for our array in the same position where we have included the newaxis object.

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

Syntax

np.array()[numpy.newaxis]

Numpy Newaxis is an object that is included in the array to expand the dimension of the given array. The dimension can be expanded in the position with respect to the position of the newaxis object.

Examples of NumPy Newaxis

Here are the following examples as mentioned below:

Example #1

Let us discuss a basic example for understanding how the numpy newaxis function works.

Code:

import numpy as np a = np.array(7) print('array: n', a) print('array.ndim:', a.ndim) print('array.shape:', a.shape) b=np.array(a)[np.newaxis] print('array: n', b) print('array.ndim:', b.ndim) print('array.shape:', b.shape)

Output:

Here in the above example, we have called the numpy library for our np.newaxis function. We have declared an array ‘a’ which is a single dimension array having only one element and the corresponding value, dimension, and shape of the array ‘a’ is printed to confirm that its a zero-dimensional array 0-D. Now to convert 0-D array to one-dimensional array we use the np.newaxis function in our new array ‘b’ and the corresponding value, dimension, and shape of the array ‘a’ is printed it confirms the expansion of the 0-D array to 1-D array.

Example #2

In this example well discuss how to change the one-dimensional array to a two-dimensional array using the newaxis object.

Code:

import numpy as np a=np.array([3,4,5]) print('array: n', a) print('array.ndim:', a.ndim) print('array.shape:', a.shape) b=np.array(a)[np.newaxis] print('array: n', b) print('array.ndim:', b.ndim) print('array.shape:', b.shape)

Output:

In this example, we have converted a one-dimensional array to a two-dimensional array by using the numpy newaxis function. We have created an array ‘a’ as a one-dimensional array and we have printed its value, dimension, and shape. The array ‘b’ is an extension of array ‘a’ with an expanded dimension using the np.newaxis object inside the tuple. The resulting array as we can see is a two-dimensional array and we have printed its value, dimension, and shape.

Example #3

In this example, we have created a zero-dimensional array and converted it into a two-dimensional array.

Code:

import numpy as np a=np.array(5) print('array: n', a) print('array.ndim:', a.ndim) print('array.shape:', a.shape) c=np.array(a)[np.newaxis,np.newaxis] print('array: n', c) print('array.ndim:', c.ndim) print('array.shape:', c.shape)

Output:

The array ‘a’ as we did in example 1 is a zero-dimensional (0-D) array and we have printed its value, dimension, and shape of the array ‘a’. Now using the numpy newaxis object in the array ‘a’ we have directly converted a one-dimensional array ‘a’ into a two-dimensional array ‘c’. We have declared the np.newaxis twice inside the tuple so it represents the expansion of dimension into two times. So the corresponding array ‘c’ which we got is a two-dimensional array.

Example #4

In this example, we’ll see how we can expand a zero-dimensional array into a three-dimensional array.

Code:

import numpy as np a=np.array(5) print('array: n', a) print('array.ndim:', a.ndim) print('array.shape:', a.shape) c=np.array(a)[np.newaxis,np.newaxis,np.newaxis] print('array: n', c) print('array.ndim:', c.ndim) print('array.shape:', c.shape)

Output:

The array ‘a’ we have created is similar to previous examples which is a one-dimensional array. The array ‘c’ we have created is an expansion of array ‘a’ into a three-dimensional array and we have done that using the numpy newaxis function thrice inside the tuple along with the array ‘a’ and the resultant array is a three-dimensional array of shape (1,1,1). So using this technique we can create n-number of dimensions in an array.

Example #5

In this example, we’ll try to expand the dimension of a one-dimensional array into a two-dimensional array along with changing the shape of the resulting array using the newaxis object.

Code:

import numpy as np a=np.array([5,10,15]) print('array: n', a) print('array.ndim:', a.ndim) print('array.shape:', a.shape) c=np.array(a)[:,np.newaxis] print('array: n', c) print('array.ndim:', c.ndim) print('array.shape:', c.shape)

Output:

In this example, we have used the same one-dimensional array ‘a’, and using the numpy newaxis function expanded its dimension into a two-dimensional array and we have converted the shape from (3, ) to (3,1). The position of the np.newaxis object is very important in determining the shape of the array. Here we have declared the newaxis object in the 1st axis.

Example #6

Code:

import numpy as np a=np.array([[2, 4, 6], [4, 8, 12]]) print('array: n', a) print('array.ndim:', a.ndim) print('array.shape:', a.shape) c=np.array(a)[np.newaxis,:,:] print('array: n', c) print('array.ndim:', c.ndim) print('array.shape:', c.shape)

Output:

In this example the two-dimensional array ‘a’ with the shape of (2,3) has been converted into a 3-dimensional array with a shape of (1,2,3) this is possible by declaring the numpy newaxis function along the 0th axis and declaring the semicolon representing the array dimension to (1,2,3). By using this technique, we can convert any numpy array to our desired shape and dimension.

Example #7

Code:

import numpy as np a=np.array([[2, 4, 6], [4, 8, 12]]) print('array: n', a) print('array.ndim:', a.ndim) print('array.shape:', a.shape) c=np.array(a)[np.newaxis,:,:] d=np.array(a)[:, np.newaxis, :] e=np.array(a)[:, :, np.newaxis] print('array: n', c) print('array.ndim:', c.ndim) print('array.shape:', c.shape) print('array: n', d) print('array.ndim:', d.ndim) print('array.shape:', d.shape) print('array: n', e) print('array.ndim:', e.ndim) print('array.shape:', e.shape)

Output:

Similar to the previous example using the one-dimensional array ‘a’ we have converted three-dimensional array ‘c’, ‘d’ & ‘e’. All three resulting arrays are three dimensions with different shapes namely (1,2,3), (2,1,3) & (2,3,1) which show us the position of our newaxis object we have declared along with the array. For array ‘c’ we have declared the newaxis object at the 1st position, for the array ‘d’ we have declared the newaxis object at the 2nd position and for the array ‘e’ we have declared the newaxis object at the 3rd position which gives the corresponding shapes.

Conclusion

In this article, we have discussed the Numpy newaxis function in detail using various examples to get a clear understanding of the numpy newaxis function and its uses. We have also discussed in detail how to use the newaxis object inside the tuple to convert the array into multi-dimensional arrays and techniques involved in changing the shape of the resulting array with examples. I hope this article helps. Thank you.

Recommended Articles

This is a guide to NumPy Newaxis. Here we discuss the Numpy newaxis function in detail using various examples to get a clear understanding of the numpy newaxis function. You may also have a look at the following articles to learn more –

Update the detailed information about Excel Vba Instr Function – Explained With Examples on the Flu.edu.vn website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!