## Loan repayment for period in Excel

Q26. In Excel, how do I get the principal part of loan payment for a period given Loan amount, Rate of Interest and period?

For example, I had taken a loan for \$150,000 @ 3% pa ROI with a period of 15 years. The first installment of the loan was paid in Nov 2015. I want to find out how much of the loan payment that I made in 1st installment has

gone towards the principal.

To do it in Excel, here is the answer:

a) Enter the formula =PPMT(D3/12,D8,D4*12,-D2) where D2 corresponds to "Debt Amount", D3 corresponds to Rate of Interest per annum and D4 corresponds to the loan period in years.

D8 is the period for which principal part of loan payment is to be computed (principal part of loan varies for every repayment period).

As you can see from screenshot, monthly repayment for the loan is \$1'035.87. In the first installment (month 1), out of \$1'035.87, \$660.87 goes towards principal and the rest towards interest for the loan.

#### Question

##### add trend indicator in a cell for sales date in Excel

Q111. I want to add a trend indicator symbol next to my sales data - how can I do that? How can I enter special symbols in my sheets?

For example, I want to show whether Sales has increased or decreased compared to previous period using Triangle / Inverted Triangle - how can I add the same?

To do it in Excel, here is the answer:

a) Click on "Symbol" under "Insert" as shown below.

b) In the "Symbol" dialog box click on the Symbol of choice and click Insert.

3) Repeat Step b for every row in "Trend" column.

#### Question

##### autocomplete text typing short code for the text in Excel

Q115. In Excel, how can I have text autocomplete by typing in a short code for the text?

For example, instead of typing in full Client Type text for every client, I would like to type in short codes like RC (RESELLER CORPORTAE), RG (RESELLER GOVERNMENT) etc., to save data entry time.

To do it in Excel, here is the answer:

a) Click on "Options" under "File". Click on "AutoCorrect Options" under "Proofing".

b) In the "AutoCorrect" dialog box, select "AutoCorrect" tab. In the "Replace:" field, enter short code. In the "With:" field enter the full Client Type. Click on "Add".

Repeat the process for every Client Type. Click OK.

c) In the WorkSheet type in the short code as shown below.

d) Excel automatically replaces the short code with the full text that was defined.

#### Question

##### vba clear cell after macro runs for this cell in Excel

Q175. In Excel, how can I clear cell after activating a routine when there is a change in value of a cell?

For example, I want to filter records pertaining to the person name specified in cell AV2. Whenever name in AV2 changes, I want to apply filter based on name selected and then copy the filtered data and paste it in cell AT4.

Then I want to clear the name without affecting the filtered data that has been copied over into cell AT4.

To do it in Excel, here is the answer:

1. Option Explicit
2. Private Sub WorkSheet_Change(ByVal Target As Range)
3. Dim KeyCells As Range
4. Dim sName As String
5. ''
6. Set KeyCells = ActiveSheet.Range("\$AV\$2")
7. If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
8. ''
9. sName = ActiveSheet.Range("\$AV\$2").Value
10. ''
11. 'Clear existing data in Destination.
12. ActiveSheet.Range("AT4:AV14").Clear
13. ''
14. 'Filter rows based on Name which is Field 2 (Col AQ).
15. ActiveSheet.Range("AP4:AR4").AutoFilter
16. ActiveSheet.Range("AP4:AR14").AutoFilter Field:=2, Criteria1:=sName
17. ''
18. 'Copy filtered table and paste it in Destination cell.
19. ActiveSheet.Range("AP4:AR14").SpecialCells(xlCellTypeVisible).Copy
20. ActiveSheet.Range("AT4").PasteSpecial Paste:=xlPasteAll
21. Application.CutCopyMode = False
22. ''
23. 'Remove filter that was applied.
24. ActiveSheet.AutoFilterMode = False
25. ''
26. 'Disable Events to avoid clearing of Target cell contents from activating this Worksheet_Change routine again.
27. Application.EnableEvents = False
28. ''
29. Target.ClearContents
30. ''
31. 'Enable Events
32. Application.EnableEvents = True
33. ''
34. End If
35. End Sub

Description:

a) The above code has to be included in the module of the "Sheet" in which the input Table resides.

b) Line 2 - Whenever there is change in any cell in the sheet, this routine is executed.

c) Line 7 - Check if the change in WorkSheet pertains to cell of interest (AV2 in this case). If yes, execute code to filter, copy filtered data, paste it in destination and remove filter (Lines 7-23).

d) Line 27 prevents the code from entering into infinite loop by preventing "Worksheet_Change" routine from being activated when Target cell content is cleared.

#### Question

##### get percentile rank for students in Excel

Q57. I conducted a test and the test scores are available - how can I get Percentile rank of students?

To do it in Excel, here is the answer:

a) Enter the formula =PERCENTRANK(\$D\$3:\$D\$22,D3,2) in "Percentile Rank" column for first student. Drag the formula all the way down to end of column.

The first argument in formula corresponds to the array containing the scores, the second argument corresponds to individual Student score and the 3rd argument define the number of significant digits in the result.

#### Question

##### loan repayment for year in Excel

Q27. In Excel, how do I get the principal part of loan payment for the year given Loan amount, Rate of Interest and period?

For example, I had taken a loan for \$150,000 @ 3% pa ROI with a period of 15 years. The first installment of the loan was paid in Nov 2015. I want to find out how much of the loan payment that I made in 2016 has gone towards the principal.

To do it in Excel, here is the answer:

a) Enter the formula =CUMPRINC(J3/12,J4*12,J2, J8, J9, 0) where J2 corresponds to "Debt Amount", J3 corresponds to Rate of Interest per annum and J4 corresponds to the loan period in years.

J8 and J9 corresponds to the installment period for Jan 2016 and Dec 2016. The first installment (monthly) was in Nov 2015. Hence Jan 2016 and Dec 2016 correspond to 3rd and 14th installments.

The last argument "0" used defines when installments are due: 0 = end of period. 1 = beginning of period.

As you can see from screenshot, Yearly repayment for the loan in 2016 is \$1'035.87*12 = \$12'430.44. Of this amount, \$8'080.68, goes towards principal and the rest towards interest for the loan.

#### Question

##### calculate loan amount based on interest rate and period in Excel

Q25. In Excel, how do I calculate monthly payment given Loan amount, Rate of Interest and period?

To do it in Excel, here is the answer:

a) Enter the formula =PMT(D3/12,D4*12,-D2) where D2 corresponds to "Debt Amount", D3 corresponds to Rate of Interest per annum and D4 corresponds to the period in years.

#### Question

##### find field value for specific value in Excel

Q24. Given a raw data Table, how can I find the value of a field for a specific value of another field?

For example, from a class marks table, given a Student First Name, how do I get his / her marks?

To do it in Excel, here is the answer:

a) Enter the formula =INDEX(V3:V12,MATCH(Y3,T3:T12)) where T3:T12 corresponds to all First names in Table, V3:V12 has marks data and Y3 corresponds to the first name of student for whom the marks detail is required.

MATCH returns the relative position of the cell in the "First Name" range that matches the user specified "First Name". INDEX function uses this value as Index to get the marks from range with "Marks".

#### Question

##### text histogram for value in Excel

Q12. Can I add a Text histogram to Table data to make it visually appealing and easy to interpret?

For example in the table below instead of reading individual "Actual vs Target" values, can I represent them in a histogram fashion so that it is easy to identify who has exceeded target and who has not met target (also by how much).

To do it in Excel, here is the answer:

a) As a first step, create a column for capturing "Salesperson" name. To the left of "Salesperson" column add a column "Below Target". To the right add a column "Exceeded Target".

Enter the formula =C5 & " " & D5 to get the name of the Salesperson in the first row of "Salesperson" column. Copy the formula for the rest of the rows.

b) Select the font "Wingdings 2" for the "Below Target" column. Repeat the step for "Exceeded Target" column.

c) Enter the formula =IF(I5<0,REPT(CHAR(162),-1*I5/1000),"") in first row in "Below Target column. This formula repeats (REPT function) Windings 2 CHAR 162 (-1*I5/1000) times in the cell if I5 value is negative signifying "Below Target"

Copy the formula for the rest of the rows.

d) Enter the formula =IF(I5>0,REPT(CHAR(162),I5/1000),"") in first row in "Exceeded Target" column. This formula repeats (REPT function) Windings 2 CHAR 162 (I5/1000) times in the cell if I5 value is positive signifying "Exceeded Target".

Copy the formula for the rest of the rows. The table will look like the screenshot below. It is now easy to infer and compare performance of different Salespersons.

#### Question

##### project management find end date for task in Excel

Q55. I am using Excel for Project Management - how can I find End Date for a task given Start Date and Task duration considering only working days?

For example, I have Start Date and Task duration for my project tasks. In Excel, how can I get End Date for tasks considering only working days?

To do it in Excel, here is the answer:

a) Enter the formula =WORKDAY(F4,E4) in End Date column for first task. Drag the formula all the way down to end of column.

The first argument in formula corresponds to Start Date and the second argument corresponds to task duration.

End dates are updated for all tasks as shown below.

#### Question

##### day of week for date in Excel

Q18. In Excel, how can I find the day of the week given a date?

For example, the day of the week of 01st Jul 2017.

To do it in Excel, here is the answer:

a) Enter the formula =TEXT(AG2,"dddd") where AG2 contains the date for which day of the week is to be determined.