MS Advance Excel Course

“PMT” stands for “payment”, hence the function’s name. The PMT Function is categorized under financial functions in MS Excel. This function helps you to calculate the EMI (principal and interest) required to settle a loan or an investment with a fixed interest rate over a specific time period. Syntax: =PMT(rate, nper, pv, [fv], [type]) Arguments: Rate : interest rate of the loan. Nper : (Duration) total number of periods over which the loan should be paid. Pv : The present value, or total value of all loan payments now. Fv : (optional argument) – This is the future value or a cash balance we want to attain after the last payment is made. Type : (optional argument) – The type of day count basis to use. The possible values of the basis are: 0 = end of period. 1 = beginning of period. Default is 0. Calculate Yearly Interest Rate EMI

The PROPER function in Microsoft Excel is used to capitalize the first letter of each word in a given text string. It converts the rest of the letters in each word to lowercase. This function is especially useful for formatting names, titles, or any text where proper capitalization is required.   Syntax =PROPER(text) text: This is the text string that you want to convert to title case (capitalize the first letter of each word). It can be a reference to a cell containing text or a text string enclosed in double quotation marks. Example

The LOWER function is a text function used to convert all the letters in a text string to lowercase. This function is helpful when you need to ensure that text is consistently in lowercase format, regardless of how it was originally entered. Syntax =LOWER(text) text: This is the text string that you want to convert to lowercase. It can be a reference to a cell containing text or a text string enclosed in double quotation marks. Example

Learn how to easily convert text to capital letters using Excel’s UPPER function. 🧠 क्या सीखेंगे: What is UPPER Function? Why is it useful? How to use it with examples? Practice Assignments The UPPER function is a text function that is used to convert all the letters in a text string to uppercase. This can be useful in various situations where you need to ensure that text is consistently in uppercase format, regardless of how it was originally entered. Syntax =UPPER(text) text: This is the text string that you want to convert to uppercase. It can be a reference to a cell containing text or a text string enclosed in double quotation marks. Example   📝 Student Assignments for Practice Assignment 1: Name Capitalization Objective: Convert all names to uppercase. Data: Name amit kumar sneha mehta deepak joshi priya singh 👉 Task: Use the UPPER function in a new column to convert each name into capital letters. Assignment 2: Product Titles in Uppercase Objective: Standardize product titles. Data: Product Name wireless mouse gaming keyboard laptop bag bluetooth speaker 👉 Task: Use the UPPER function to convert all product names into uppercase for a professional look.

When you invest your money such, you can earn interest on your investment amount. for example, you invest $3,000 with a 10% annual interest rate, compounded annually. One year from the initial investment (called the principal), you earn $300 ($3,000 x 0.10) in interest, so your investment is worth $3,300 ($3,000 + $300). For the next period, you earn interest based on the gross figure from the previous period: $330 ($3,300 x 0.10) because your investment was worth $3,300. Now, it is worth $3,630. Formula : =B27*(1+A27) =C27*(1+A27) The general formula for compound interest is: FV = PV(1+r)n, where FV is future value, PV is present value, r is the interest rate per period, and n is the number of compounding periods.   How to Calculate Compound Interest in Excel If you are investing $1,000 with a 10% interest rate, compounded annually, below is how you would calculate the value of your investment after year. Formula : =B27*(1+A27) =C27*(1+A27) Compound Interest Calculation Using FV Function In Excel you can calculate compound interest using fv function in Excel. FV function calculates the future value of an investment based on the values of certain variables. Formula : =FV(B52/B53,B53*B54,0,-B51)

Len function is used to measure the length of text in excel. Len function… measures the length of the text by counting all the characters. Along with the characters inside the Len function, if any space is given in the cell or special characters, symbols or numbers, then it also counts them. Syntax- =Len(text) Argument- Text – We write here the text of the cell for which the length of the text is to be extracted, or we can also select the cell reference of the text.   How to use LEN function in excel As you can see in the above image that the name is written in A column and in B column we have extracted the length of the name. After this, if you see in the image below, some special characters have also been used here. |

TRIM () Function In Excel This is a Pre-Defined Function of Excel and it is used to remove extra space from Text or Characters in Excel. The TRIM function keeps only one space between the text or characters and finds and removes all the extra blank space. Syntax- =TRIM(text) Arguments- Text– In this argument, we select that text or type the text from where we have to remove extra spaces. As you can see in the image below that we have a data in which some names are given. And now all the extra spaces in between those names have to be removed. So in such a situation you can use Trim Function and you can easily delete the extra space. So if you see here, the extra space in the middle of the text in the name column has been removed by the trim function and now there is only one space. Apart from this, if you want, you can also apply it in the numbered cell as you can see in the image below. In this way, you can remove extra spaces using Trim function in Excel. If you have any question or suggestion related to Trim function, then write a comment in the comment box.

Concatenate Function in Excel is placed under Text Category and this function is used to join two or more values ​​(Characters, Strings or Numbers) together. Like- First Name and Last Name are given in two different columns and if you want to merge them in one column then you can use Concatenate function in excel here. We can also apply the Concatenate function to Characters, Strings or Numbers. Syntax- CONCATENATE (text1,[text2],….) In the image below, you can see that column A has first name and column B has last name and we have to add both in column C to get the same cell C using Concatenate function. Function : =CONCATENATE(A2,B2) As soon as you apply the formula shown above and press the enter button, adding both the names in front of you will be displayed inside the cell where you have applied the formula.   Now you will see here, we have CONCATENATED both the names in one column, but it is not so easy to read because there is no space between the two values, so now what we want is that there should be a space between the two names. So it will be easily readable, so let’s see how we can give space between two names. You have to make some changes in your formula Before =CONCATENATE(A2,B2) Now =CONCATENATE(A2,” “,B2) In the same way, if you have more than two values ​​in your data to concatenate, then you will be able to add them using this formula like –   Alternative Of Concatenate Formula

IF ERROR Function In Excel In Hindi, Microsoft – IF ERROR in Excel is a pre-defined functions IF ERROR Function is placed under the category of logical function in Excel. IF ERROR FUNCTION is used to display the correct result or message in response to the error of a FUNCTION. IF ERROR Function in Excel is used to hide errors. Whenever we use any function in excel and if there is some mistake in it. So due to these mistakes errors start appearing in the result. Some errors can be corrected in MS Excel by using If Errors, so in this case you can use IF ERROR Function to hide your Errors. Syntax- =IFERROR(value, value_if_error) ARGUMENTS- value- In this argument we give formula or reference which is checked by if error function whether there is any error in it or not. value_if_error – If there is an error in the value or formula, the result you want to be displayed in the cell is defined here.   Types Of Error In Excel 1. #NULL! error This error is due to difficulty in the argument partitioning of the function, Excel returns the #NULL! The error appears. Like: SUM (A1:B5 C1:D5) etc.   2. #DIV/0! error In this error, the function of the cell is being divided by the value 0 present in an empty cell or cell, due to which the error is seen in it.3. #VALUE! error The reason for getting such error is that both text and number are being equaled, then such error occurs like : =A +15 etc.   3. #REF! error In this way ( #REF! ) the main reason for the error is the absence of the address of the function in the cell, that is, the cell mentioned in the function is deleted like : =SUM(A1,B2) If you delete the cell, the function #REF! will give an error of   4. #NAME? error This (#NAME?) error means the invoked function name is incorrect. Or you are adding direct text and numbers like: =VLOOKU(A1,B1,2,0) , = 15+A etc.   5. #NUM! error The reason for such error occurs when a value is not eligible to be calculated, then the function gives us the #NUM! provides. Ex: =SQRT(-16) Ans.: #NUM!   6. #N/A error This type of error comes when there is a miss match in the function, then Excel provides this type of error. Which can be used to hide the error using If error Function in Excel.

FV, one of the financial functions which is calculates the future value of an investment based on a constant interest rate. You can use the FV function to get the future value of an investment assuming periodic, constant payments with a constant interest rate. Syntax =FV(rate, nper, pmt, [pv], [type])   Arguments rate – The interest rate per period. nper – The total number of payment periods. pmt – The payment made each period. Must be entered as a negative number. pv – [optional] The present value of future payments. type – [optional] When payments are due For example, suppose you invest $10,000 at an annual interest rate of 5% for 10 years and make monthly payments of $100. You can use the FV function to calculate the future value of your investment as follows: =FV(5%/12, 10*12, -100, -10000) The first argument 5%/12 converts the annual interest rate to a monthly rate. The second argument 10*12 converts the total number of years to the total number of months. The third argument -100 represents the monthly payment you make, which is negative because it represents an outgoing payment. The fourth argument -10000 is the initial investment, which is also negative because it represents an outgoing payment. The FV function will return the future value of your investment after 10 years