Free Microsoft Excel 2013 Quick Reference

Recently I used the amortization schedule designed by Microsoft. It's

fantastic. However, it will be more friendly and convenient for the users if

at the end of each year the user can see a subtotal for the interest paid,

principal paid, etc.

Actually, I tried to get those subtotals by inserting lines under the last

payment of each year, however that will result in different amounts of the

cumulative interest colum mainly becasue of the default set formulas.

Hopefully, we can see the improvement soon.

----------------

This post is a suggestion for Microsoft, and Microsoft responds to the

suggestions with the most votes. To vote for this suggestion, click the "I

Agree" button in the message pane. If you do not see the button, follow this

link to open the suggestion in the Microsoft Web-based Newsreader and then

click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...heet.functions

fantastic. However, it will be more friendly and convenient for the users if

at the end of each year the user can see a subtotal for the interest paid,

principal paid, etc.

Actually, I tried to get those subtotals by inserting lines under the last

payment of each year, however that will result in different amounts of the

cumulative interest colum mainly becasue of the default set formulas.

Hopefully, we can see the improvement soon.

----------------

This post is a suggestion for Microsoft, and Microsoft responds to the

suggestions with the most votes. To vote for this suggestion, click the "I

Agree" button in the message pane. If you do not see the button, follow this

link to open the suggestion in the Microsoft Web-based Newsreader and then

click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...heet.functions

- Array or macro for NVP of a loan amortization schedule?
- Loan amortization schedule
- Loan amortization schedule
- Loan Amortization Schedule Template from Business Financial Plng.
- Loan amortization schedule with bi-monthly payments
- Loan Amortization Schedule Template from Business Financial Plng.
- How can I combine loan amortization schedules
- Need 360-day loan amortization excel spreadsheet template
- Calculate the growth rate for each year
- Loan Amortization with Increases In Principle
- Loan Amortization in a form
- Loan Amortization Formula
- Subtotals for Pivot Table
- Premature exit from for-each loop makes no sense
- Subtotal For Each Group
- An interesting Summing For Each Loop
- Loan amortization schedule, rule of 78
- Is there an adjustable rate amortization schedule in Excel?
- Loan amortization- how can I enter 4.75 years? no a whole number
- Loan amortization
- For Each loop; worksheets.range
- Amortization schedule with mo. pmts, but interest calc daily
- Loan amortization schedule
- Calculate absolute values for each date (group sum)

Thanks for any and all replies.

interest rate each time "prime" changes and there is only one payment on June

1st of each year for 6 years

interest rate each time "prime" changes and there is only one payment on June

1st of each year for 6 years

The template uses a condition in its IF formulas that requires a number of

cells be populated in order for the formula to run through its calculation.

An example is =IF(Values_Entered,A316+1,"") where cells d5-d7 and d9 had to

be populated or the cell is left blank. Was wondering how that is set up

where the IF formula looks to those cells to verify that there is data.

Also, "Values_Entered" does not appear in the drop box for the range names.

Thanks in advance for your assistance.

http://office.microsoft.com/en-us/te...197771033.aspx and found

that the Payment Dates won't calculate for 24 payments per year (e.g. 1st and

15th payments). In stead of 1/1/2005 followed by 1/15/2005 I get 1/1/2005

followed by 2/1/2005 followed by another 2/1/2005. I believe the error stems

from the "DAY(Loan_Start)" portion from the formula, but it could come from

"(Pay_Num)*12/Num_Pmt_Per_Year" yielding 0.5 payment increments.

The original formula is:

=IF(Pay_Num"",DATE(YEAR(Loan_Start),MONTH(Loan_S tart)+(Pay_Num)*12/Num_Pmt_Per_Year,DAY(Loan_Start)),"")

The template uses a condition in its IF formulas that requires a number of

cells be populated in order for the formula to run through its calculation.

An example is =IF(Values_Entered,A316+1,"") where cells d5-d7 and d9 had to

be populated or the cell is left blank. Was wondering how that is set up

where the IF formula looks to those cells to verify that there is data.

Also, "Values_Entered" does not appear in the drop box for the range names.

Thanks in advance for your assistance.

at a different date, AS OF the same date. I need total PV, Total Interest

paid during the year. Any ideas ?

year. Does anyone know where I can get a template for this?

month (Multiple Items)

Values

Row Sum of Net value The Growth Rate

2007 14,378,194

2008 2,762,771 #DIV/0!

2009 3,595,793 #DIV/0!

2010 624,715 #DIV/0!

Grand Total 21361473.11

I am trying to calculate the growth rate for each year by doing the following:

Value field settings ----> summarized by (sum) ----> show value as ----> % difference from (Base field:year,Base Itemrevious)

But I keep getting the error shown in the table above DIV/0

I guess because the fist year GR equals zero.

I don't how to overcome this problem.

And I don't know if this is the right way to calculate the Growth Rate to start with.

Thanks.

Kimberly

Im in the process of putting together a home finance system for my university project and one of the key requirements of the system is that the system should avoid the need for the user to interact directly with the spreadsheet. So currently everything it does is through forms.....e.g. Add accounts, add transactions to accounts etc.

Now I need to somehow implement the loan amortization feature and I was wondering if its possible to somehow 'addin' a spreadsheet loan amortization schedule the system creates on to a form which the user can interact with? (instead of directly with the spreadsheet)

If this is not possible anyone got any others ideas on how it might be possible to display the loan schedule?

Many Many Thanks

I want to show an annual loan interest calculation that is based on a

monthly amortization schedule.

The problem is that each month we repay some of the principal and then next

months interest is calculated on a different principal amount.

Illustrated I am imagining that this is how this would look assuming we

start with a balance of $1,000 and an monthly interest rate of r.

1,000*r+(1,000-((1,000*r))*r+(1,000-(1,000-((1,000*r))*r)...and so on

This seems like some sort of math series and I am wondering if this can be

simplified in a formula.

If anyone can help I would really appreciate it.

Thanks.

Neda

I have a for each loop that exits when it shouldn't be. (Or so I think)

cRunSheets is a collection of the names of the sheets that are relevant to this procedure. Its a public variable.

I've validated it while the code was in break mode, there are 15 items in the cRunSheets collection. The loop should run 15 times.

Immediate window as the loop starts.

VB:?cRunsheets.Count 15If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Basically, I want it to find the sheet I send the procedure (sentSheet) and clear the contents of that range on every subsqent sheet to the end of the collection, but nothing before the sheet it is sent. I am not a fan of using the sheet index, I would rather refer to it by name. There are many sheets in the workbook but my collection only gathers the ones I want.

getSheets calls the collection routine to populate from the workbook.

VB:So this runs/compiles fine, I send it the 3rd sheet. It loops 1, 2 gets to 3 flips afterCurrent to True, executes the 2nd if block and clears the range. Then it goes to End Sub, always quits when afterCurrent gets flipped.) Dim wsFlagSet As Worksheet Dim parameter As Integer Dim runSheet As Variant Dim afterCurrent As Boolean afterCurrent = False 'Sets parameter based on information passed. (Sheet = single sheet, forward = all sheets after) If IsMissing(sentSheet) = True And forward = False Then parameter = 1 ElseIf IsMissing(sentSheet) = True And forward = True Then parameter = 2 ElseIf IsMissing(sentSheet) = False And forward = False Then parameter = 3 ElseIf IsMissing(sentSheet) = False And forward = True Then parameter = 4 End If Select Case parameter Case 1 '//Nothing passed blow up everything For Each wsFlagSet In ActiveWorkbook.Worksheets If Mid(wsFlagSet.Name, 1, 4) = "BRUN" Then wsFlagSet.Range("H22:AK22").ClearContents End If Next wsFlagSet Case 2 '//makes no sense but blow up everything here too. For Each wsFlagSet In ActiveWorkbook.Worksheets If Mid(wsFlagSet.Name, 1, 4) = "BRUN" Then wsFlagSet.Range("H22:AK22").ClearContents End If Next wsFlagSet Case 3 '//Just a sheet passed, clear one sheet Sheets(sentSheet).Range("H22:AK22").ClearContents Case 4 '//Sheet passed, forward passed clear sheet and everything to the end getSheets For Each runSheet In cRunSheets If runSheet = sentSheet Then afterCurrent = True End If If afterCurrent = True Then Sheets(runSheet).Range("H22:AK22").ClearContents End If Next runSheet End Select End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I feel silly for asking because this is programming 101, controlling loops and I just can't see what the problem is. As always, I am sure there is a different way to do this and I can work around it. However now it is an issue of prinicple and I have to figure out why this speciffic instance does not work because as written (to me) it should.

Pay no attention to the fact I used the workbook.sheets collection to loop on everything and then changed to my own collection... :D I'll be fixing that after I restore functionality...maybe.

Thanks in advance!!

1. I need to pick up the first item in sheet 1.

2. Find the column which contains "Module Name" in sheet 2.

3. Search for the item picked up from sheet 1 in the column which contains "Module Name" in sheet 2.

4. Sum up the "Base Price" for all occurrences and enter this summed value into column B of sheet 1 next to each item.

I have no idea about how to even get started on this so i'd really appreciate some help.

I've attached the excel sheet to give a better idea of my query.

thanks

Attached you will find a sample file.

I am trying to have a for each loop that will look at each product and then go through each fund in the product and see which strat it is and add it to that and then see which sub strategy it is and add it to that.

For example,

Product 1, Fund 3 is part of Strat 1 and sub 1, so it should be added to product 1's total for strat 1 and sub strat 1, etc.

Any questiosn for clarification please ask.

Cheers

FYI this is a cross post from excelforums.

I'm having a hard time formulating an amortization schedule, where interest is calculated via rule of 78, not simple interest. AmorizeIT does it perfectly, however its not an excel sheet that i can see formulas in.

has anybody done thing before?

cheers

interest rates after a few years?

using is for 4.75 years (4 years 9 months). The template will not allow me

to use 4.75 years, it will only accept whole numbers between 1 and 30. Does

anyone have any suggestions on how I can get around this? Thank you!

need 2 separate loan amortization schedules, but the second schedule won't

run separateky. It picks up detail from the first due to the formula format.

Any idea on how to override or what to do?

Sub populateSchI() Dim chargeNo As Range Dim concat As Range Set chargeNo = Worksheets("Schedule I").Range("A6") For Each concat In Worksheets("2008").Range("D:D") If concat.Value = chargeNo.Value Then concat.Offset(0, 7).Value = "It worked" End If Next concat End Sub

made with fixed monthly payments, but the interest is calculated on a daily

basis. I guess similar to a credit card.

rate and term for a loan to get a amortization table. Also if one of the

four above are missing it will calculate the missing number.

I have the following simple problem setup for two columns let's say A and B:

01-02-2011 -534

01-02-2011 53

01-02-2011 43

02-02-2011 25

02-02-2011 -3523

02-02-2011 532

02-02-2011 3

I need to calculate the absolute value for each date in the summing of column B.

for example if u sum the whole column B as it is above then you get -3401. What I want is not to get rid of this minus-sign. But i want to get rid of the minus for each date-sum, for instance:

01-02-2011 -534

01-02-2011 53

01-02-2011 43

the output/number i want from this is abs(-534+53+43)=438

then for

02-02-2011 25

02-02-2011 -3523

02-02-2011 532

02-02-2011 3

abs(25-3523+3253+532+3)=2963

so the total is 2963+438=3401

can any help? ... should i use for-each construction or is it not simpler??

thanks.