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

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

rate staff for bonus in Excel

Q62. I have to determine bonus for my staff based on ratings (for each rating, specific bonus amount). In Excel, how can I set up my Bonus Table?

For example in the table below I have the ratings of all my staff. I would like to set up the table for automatic bonus calculation based on the ratings -

Ratings 1 - \$1200, 2 - \$800, 3 - \$400 and 4 - \$0.

To do it in Excel, here is the answer:

a) Enter the formula =CHOOSE(U4,1200,800,400,0) in "Bonus" column for first employee. Drag the formula all the way down to end of column.

The first argument is the Index to choose from one among many values entered in the subsequent section of formula.

For example, for the first employee, rating is 2. Hence second value 800, from among the set of values (1200, 800, 400, 0) is selected by formula.

Question

day number of the year in Excel

Q97. In Excel, how can I get the day number of the year for a date - for example 5 for Jan 5, 33 for Feb 2?

To do it in Excel, here is the answer:

a) Enter the formula =(K2-DATE(YEAR(K2),1,0)) where K2 corresponds to "Date" input for which day number is required.

To calculate the day number the first ate of the year has to be subtracted from specified date.

Date corresponding to the first day of the year has the same year as the specified date with month being 1 and day being 0 (representing start of year).

The function (DATE(YEAR(K2),1,0) returns the first day corresponding to the year of the Date specified. This is subtracted from the date specified to get "Day number".

The "Day number" field (cell K4) has to be formatted as number.

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

Keyboard shortcut for save as in Excel

i'm looking for a keyboard shortcut for Save As...
thank you...

The easiest save as shortcut is F12

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.

Question

Formula converts date to quarter and year in Excel

A1 contains a date formatted cell as dd/mm/yr, say 01/15/06. What formula, in B1, would convert this date to: Q1-06?

use this formula if the date in cell A1  ="Q"&(ROUNDUP((MONTH(A1)/3),0)&"-"&RIGHT(YEAR(A1),2))

(for formulas, depending on your country, you might have to change ; with , or the opposite

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

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

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.