MS Advance Excel Course

The PV function in Excel is used to calculate the present value of an investment based on a series of regular payments and a constant interest rate. The function syntax is: Syntax =PV(rate, nper, pmt, [fv], [type]) Arguments rate – The interest rate per period. nper – The number of payment periods. pmt – The payment made each period (EMI). fv – [optional] Future value. If omitted, defaults to zero. type – [optional] Payment type, 0 = end of period, 1 For example, to calculate the present value of an investment that pays $100 at the end of each month for 5 years, with an annual interest rate of 6%, and a future value of $5000, the formula would be: =PV(6%/12, 5*12, -100, 5000, 0) Note that the payment value is negative because it represents an outflow of cash. The future value and type arguments are included as 5000 and 0, respectively. The result would be the present value of the investment at the beginning, which would be the amount you would need to invest today to achieve the future value of $5000 after 5 years.  

The PPMT Function is categorized under Excel Financial functions.  The PPMT function in Excel is used to calculate the principal payment of an investment in a specific payment period based on a series of regular payments and a constant interest rate. The function syntax is: Syntax =PPMT( rate, per, nper, pv, [fv], [type] ) Arguments: Rate – This is the interest rate per period.Per – The payment period of interest.Nper – The total number of payment periods.Pv – This is the present value of the loan/investment.Fv (optional) – Specifies the future value of the loan/investment at the end of nper payments.Type (optional) – This specifies whether the payment is made at the start or the end of the period. For example, suppose you want to calculate the principal payment made in the 5th payment period of an investment that pays $100 at the end of each month for 5 years, with an annual interest rate of 6%, and a present value of $5000. The formula would be: =PPMT(6%/12, 5, 5*12, 5000, 0, 0) The result would be the principal payment made during the 5th payment period, which would be the amount that goes towards reducing the outstanding balance of the investment. What is the difference between PPMT and PMT? Whereas the PMT function tells you how much each payment will be, the PPMT function tells you how much of the principal is being paid in any given pay period.

The IPMT Function is categorized under Financial functions in MS Excel. The IPMT function in Excel is used to calculate the interest payment of an investment in a specific payment period based on a series of regular payments and a constant interest rate. The function syntax is: Syntax =IPMT(rate, per, nper, pv, [fv], [type]) Arguments: rate – The interest rate per period. per – The payment period of interest. nper – The total number of payment periods. pv – The present value, or total value of all payments now. fv – [optional] The cash balance desired after last payment is made. type – [optional] When payments are due. IPMT formula for different payment frequencies (weeks, months, quarters, yearly) Weekly: =IPMT(6%/52, 1, 2*52, 20000) Monthly: =IPMT(6%/12, 1, 2*12, 20000) Quarterly: =IPMT(6%/4, 1, 2*4, 20000) Semi-annual: =IPMT(6%/2, 1, 2*2, 20000)   What is difference between PMT and IPMT in Excel? Whereas the PMT function tells you how much each payment (EMI) will be, the PPMT function tells you how much of the principal is being paid in any given pay period (EMI).

The SUBSTITUTE function in Excel is a text function that allows you to replace instances of a specific text string within another text string. This can be useful for cleaning up and formatting text data, such as correcting misspelled words or replacing certain characters with others. The SUBSTITUTE function cannot replace more than one string at a time. The SUBSTITUTE function is case-sensitive and looks for instances of the string in the cell.Substitute function in excel replaces any character of selected cell value with anything we want. Syntax  =SUBSTITUTE(text, old_text, new_text, [instance]) Arguments 1. text – The text to change. 2. old_text – The text to replace. 3. new_text – The text to replace with. 4. instance – [optional] The instance to replace. If not supplied, all instances are replaced.

The REPT function in Excel is a text function that allows you to repeat a given text string a specified number of times Syntax =REPT(text, number_times) Arguments 1. text: text string that you want to repeat. 2. number_times: the number of times that you want to repeat the text string. Example

In Microsoft Excel, the keyboard shortcut CTRL+E is used to activate the Flash Fill feature. Flash Fill is a tool that automatically fills values in a column based on a pattern that it recognizes in the adjacent column. For example, if you have a list of names in a column and you want to extract the first name and last name into separate columns, you can use Flash Fill to do this quickly. Here’s how: 1. In a new column next to the column containing the names, enter the first name of the first person in the list. 2. Press the CTRL+E keyboard shortcut. 3. Excel will automatically recognize the pattern and fill in the remaining first names in the new column. 4. Repeat steps 1-3 for the last names. Flash Fill can also be used for a variety of other tasks, such as formatting dates or times, splitting addresses into separate columns, or combining data from multiple columns into a single column.

The FIND function in Excel is a text function that allows you to find the position of a specified substring within a text string. It can be used to manipulate text data in various ways, such as extracting parts of a text string or replacing specific characters within a text string. Syntax  =FIND(find_text, within_text, [start_num]) Arguments  find_text – the text string that you want to find within_text  – where you want to start searching start_num – the position within “within_text” where you want to start searching for

MID Function splits the data from the middle of the character. If you want to remove any character from left or right from a cell in Excel, then you should use LEFT Function, right function. Syntax =MID (text, start_num, num_chars) Text: the text string from which you want to extract characters start_num: the position at which you want to start extracting characters num_chars: the number of characters you want to extract As you can see in the image below that we have a data in which city names are given in Column A and their pin code numbers are also given. Now we have to separate the pin code from that data. And here we will use MID Function. Example : =MID(A2,9,7) Here A2 is the address of a cell in which text data is stored and we have to split from it, 9 is the starting position from where to start text splitting, and 7 is in the last It means that this mid function will display up to 7 characters from the selected cell, in the same way the rest will also be removed.

The RIGHT function in Excel is a text function that allows you to extract a specified number of characters from the right side of a text string. It can be used to manipulate text data in various ways, such as extracting file extensions or extracting last names from full names. Syntax  =RIGHT(text, [num_chars]) Arguments  text – the text string from which you want to extract characters num_chars – the number of characters you want to extract

The LEFT function in Excel is a text function that allows you to extract a specified number of characters from the left side of a text string. It can be used to manipulate text data in various ways, such as splitting names or addresses into separate fields or extracting specific parts of a text string. Syntax =LEFT(text, [num_chars]) Arguments  text –  text string from which you want to extract characters num_chars – the number of characters you want to extract