Free Microsoft Excel 2013 Quick Reference

EMI Calculation with Variable Interest Rates

Dear Friends,

I have prepared an excel sheet for calculation of EMI in accordance with the other attachment. Basically it is in relation to a loan, for which the ROI is 9.25% for the First Year, 10.25% for the next two years and 11% from 3rd Year onwards.

However, the principal amount does not come to a zero with the end of loan tenure and rather the borrower appears to pay excess.

Can you kindly review the file and let me have your comments / corrections.

Regards

DC


Post your answer or comment

comments powered by Disqus
using variable interest rates, create leasing factor for specific time periods: principal, term (months), interest rate, residual value,

[Excel 2003]
I want to calculate compound, variable interest on an account consisting of
multiple deposits of irregular amounts and and irregular deposit dates.
It would be best if it could calculate on actual calendar dates, not just
30-day months/360 days. Also, it would be best if interest could be
compounded daily, rather than any larger period.
Does anyone happen to know a way to program this or a site that I might
purchase it from?
Thanks.

Is there an Excel Template to track cash investments recieving variable
interest rates? E.G. Cash Funds.

Hi all,

I've created a spreadsheet to keep track of my modest share holdings and as a record of the past performance of closed out investments.

At present I am able to work out the exact % gain (or loss!) on an investment by taking the monetary change in value net of any fees and dividends, and expressing as a % of purchase value. Its a useful way to help work out where things have gone well, and where they haven't....

However, what I would really like to do is to work out the equivalent annual compound growth rate for any particular share, as this will enable me to make valid comparisons between investments over different time periods. So, for example, lets say I buy a share, hold it for 3 years and sell it for a total gain of 15%. You might suggest just dividing the % figure by the number of years to give an average gain of 5% per year.

However I think a truer reflection of performance is to work out compounded rates. To illustrate this further, lets take a standard interest bearing deposit account. If you had £100 in this account and earned 5% interest each year, you would end up not with £115, but with £115.76 after 3 years. The interest accumulated in all but the final year also earning interest in the subsequent years - the joy of compounding. While its easy to work out how funds accumulate over time with the rate of interest to hand, I don't have a method of performing the reverse calculation - extrapolating the interest rate from an end total and the time.

So for my aforementioned 15% over 3 years example, I can only guess the rate through trial and error, (its something just under 4.8% equivalent annual interest) - and that's just on paper. I wouldn't know where to begin when it comes to writing an excel formula to calculate this. The formula gets more complex still when you factor in time periods that are not whole years.

So, ideally, I would like to have a formula that calculates the time (in days, I guess?) an investment has been held from bought and sold dates (which I write in a format like so: 11-May-2009), and then uses this variable together with the total % gain (or loss) to work out the equivalent % rate of gain per year.

All of which will probably point to the fact that I'd earn more holding the money in a savings deposit account! If anyone can help out with this little headache, I'd most appreciate it. Thanks,

Andrew

Hi,

I know how to use basic functions in Excel, but need a solution to what I consider a tricky problem.

Our ministry is running an investment contest for our 4rth graders and here's the scenario:

You invest a fixed dollar amount of say 5000.00 dollars on January 1st 2007. Then, every day you recieve a different percentage in dividends. Say 1.2 percent on the first day, 1.41 percent the next day, 0.9 percent the third day etc.

I need a way to enter each days interest rate and understand this will be a daily entry, BUT we need ONE FIELD which is our initial investment field to add each days interest, to that single field for all the days listed. I'm not sure if that makes sense.

It is kind of like a compound interest calculation for ONE FIELD (The Initial Principal), but with daily changing interest rates.

I was told to create an open ended ARRAY, but need this figured out by Wednesday to show the kiddos how all this stuff works.

Please Help.
Eddie

Question in the title really, is there an excel function to calculate a daily interest rate from an APR? The APR is compounded daily, maybe there is another way?

Thanks

I am trying to calculate an average interest rate for the life of a 30 year bond issue, where over the life of the loan the principal paid was $1,000,000 and the interest paid was $800,000. I assume the interest is compounded continuously. Can you help?

To compute the future value of an investment by applying a constant
compounded annual growth rate (CAGR) over 8 years Excel requires that I type
in the actual interest rate for all 8 years using the FVSCHEDULE function.
FVSCHEDULE does not allow me to use a cell reference in the interest rate
array. Which means if I want to model an investment with different interest
rates - -I have to manually change all eight years of the interest rate
schedule every time. I would like Excel to allow the FVSchedule function to
use cell references for the interest rate array. Or create a true CAGR
function.

----------------
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...et.f unctions

To compute the future value of an investment by applying a constant
compounded annual growth rate (CAGR) over 8 years Excel requires that I type
in the actual interest rate for all 8 years using the FVSCHEDULE function.
FVSCHEDULE does not allow me to use a cell reference in the interest rate
array. Which means if I want to model an investment with different interest
rates - -I have to manually change all eight years of the interest rate
schedule every time. I would like Excel to allow the FVSchedule function to
use cell references for the interest rate array. Or create a true CAGR
function.

----------------
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

Am unable to find answer to this question. In one site I found EMI comparison, which calculates interest rate as i wanted but am unable to find the formula used there. Please Help!!

Hi all,
Can anyone tell me how to solve for Interest RATE with 2 (or more) payments in advance. I know I can simply deduct the number of advance payments from the original PV but that does not return the same interest rate as I get on my HP calculator or by using T-Value (a financial amortization program). The issue is that when you have 2 payments in advance, standard amortization tables assume that you would have 1 payment of zero at the end of the cashflows before the residual (FV) becomes due (to make up for the fact that the last payment was paid at the start of the loan). I successfully created formulas that calculate PMT, PV and FV with multiple payments in advance but I keep getting "#NUM!" error messages when I try to solve for RATE. I turned on "Enable Iterative Calculations" under Excel Options/Formulas but none of the formulas I've tried work. If anyone can help, I would really appreciate it.
Thanks in advance.
Thane

I am trying to resolve an issue with a "RATE" Function. I know that it is
correct, but I would like to introduce a variable. This is to have a holiday
for 6 months i.e. nil payment. To clarify:

I am trying to calculate the interest rate assumming that at the beginning
of a repayment structure there is an "holiday payment". "Holiday payment" -
This mean at the beginning of the term there would be as in this case 6
months where there is no payments. Monthy payments begin in Month 7 until
month 42 ie 36 Months.

Data Months Paid Data Holiday Data Total
Payment holiday in months 6 6
Months 36 36
Total Term of Loan 36 6 42
Monthly payments £24,000.00 £0.00 £20,571.43
Loan £705,270.00 £705,270.00 £705,270.00

Rate Calculated 13.69% -1200.00% 11.78%

Interest Paid £158,730.00 -£705,270.00 £158,730.00

Interest per Month £4,409.17 £3,779.29
Capital per Month £19,590.83 £16,792.14
£24,000.00 £20,571.43

The attached spreedsheet explains it better and shows the way in which the
Function is written.

Thank you for any help you can give.

Hi,

I am trying to calculate the interest rate required for a loan of 200,000 with 60 monthly repayments of 6,500 to leave a residual of 60,000.

The rate is approximately 4.88%, calculated by trial and error in a 60 row worksheet, but is there a formula to calculate this figure direct?

Thanks in advance.

Bryan

I am trying to resolve an issue with a "RATE" Function. I know that it is
correct, but I would like to introduce a variable. This is to have a holiday
for 6 months i.e. nil payment. To clarify:

I am trying to calculate the interest rate assumming that at the beginning
of a repayment structure there is an "holiday payment". "Holiday payment" -
This mean at the beginning of the term there would be as in this case 6
months where there is no payments. Monthy payments begin in Month 7 until
month 42 ie 36 Months.

Data Months Paid Data Holiday Data Total
Payment holiday in months 6 6
Months 36 36
Total Term of Loan 36 6 42
Monthly payments £24,000.00 £0.00 £20,571.43
Loan £705,270.00 £705,270.00 £705,270.00

Rate Calculated 13.69% -1200.00% 11.78%

Interest Paid £158,730.00 -£705,270.00 £158,730.00

Interest per Month £4,409.17 £3,779.29
Capital per Month £19,590.83 £16,792.14
£24,000.00 £20,571.43

The attached spreedsheet explains it better and shows the way in which the
Function is written.

Thank you for any help you can give.

Hi - I need to write a function that takes, as input:

Start date
End date
Initial deposit

I have a table that has the interest rate (APR) for each year. The dates will have a resolution of days. I need to calculate out how much money would be in an account on the end date if the initial deposit had been put in on the start date.

I can imagine a very brute force method of writing the script that does this - but does Excel have any nice built in functions for this?

I'm imagining something like:

Find interest earned from start date to end of year. I would need to extract out the exact year [YEAR(start date)] and then the number of days remaining in the year [something like ["12/31/" & YEAR(start date) - start date]]. I would then need to figure out the number of days in that year, so I guess that would look like: [("12/31/" & YEAR(start date - 1)) - "12/31/" & YEAR(start date))] and then I'd multiple the interest rate by the number of days remaining in the year divided by the number of days in the year. I'd multiply my initial deposit by that product.

Then I'd continue multiplying the products by the interest rates and I could keep on going but I think you get the idea.

This seems really brute force. Surely there is a better way.

In engineering economy problems we want to know the rate of return on an investment that returns a series of equal annual palyments for n years. This is a simpler problem than the IRR function is designed for, since it deals with varying payments which must be spelled out in an array.

I don't want to put the same annual payment in 20 cells to use IRR, and I want the number of years to be a variable, which IRR would not lend itself to. Rate should be able to do this task in a single cell, but the results I am getting are not accurate.

Basically I want to find the interest rate that will make the present value of the annual payments equal to the initial investment.

looking for a free online (or template) that can generate a full amortization
schedule when one variable (usually the interest rate) is unknown. I've
tried bankrate.com but its version can't handle a date prior to current date.
First template I downloaded from microsoft.com didn't have the ability to do
the calculation and print out a schedule. Have numerous clients with loans
that I need to have this schedule in order to apply principal/interest on
monthly payments.

I need to caclulate an amount due to my client where there is an increase in
the amount owed each month (amounts vary) and an interest rate that is tied
to the prime rate. No payment to be made until the end of the term.

I have a mortgage loan $125,000 and I pay $300 off loan every two weeks, the fixed rate is 8.50%. I am locked in at this rate for next 24mths (2yrs).
The current variable rate is 6.25% and to break from the existing fixed rate contract will cost me $3,200.
Is there a formula in Excel that I can use to compare the two rates and loan amounts using the 2yr period?
i.e. $125,000 @ 8.50% for the next 2yrs with repayments of $300 every two weeks compared to
$128,200 @ 6.25% for the next 2 yrs with repayments of $300 every two weeks. Is it worth changing over?

Many thanks

Carl

Hello.

I am calculating Daily compound interest on a Principal from a % p/a rate. I think I may have the right equation, however, I am not 100% confident with the answers.

The principal amount is $20,776.00, the interest rate is 4% p/a, the number of days is 14.

The equation I am using is = $20,776.00*(1+(4%/365))^14 = $20,807.90 I am pretty sure this is right.

However, where things go iffy is when I need to make deposits into the account. The 1st deposit is $43,505.00, this brings the balance to $64,312.90 and is made at the end of the 14 day period and the next is 28 days later. So my next equation look like this:
$64,312.90*(1+(4%/365))^28 =$64,510.53.

This goes on over another 10 months or so with days between deposits changing.

Am I doing this right? If not, how should I be doing it?

Thank you

I am trying find a functon which will help me with the following:

I have a client who made a loan to a friend in August of 2003 of 5000 (principal) with an interest rate of 10%.

I can calculate the total amount due using =FV without problem. What I'd like is to break it down by year - so, for example, B1 has original principal (5000), b2 has after year 1, B3 year 2, and so on...

Any help would be great

Thanks!!

Hi!

I have a mortgage problem that I simply cannot seem to figure out.

Scenario - I started doodling with buying a property to let and then started thinking about adding the "shortfall" between the required bond payment and the income generated by the property back onto the loan each month as the shortfall arises. Let's assume that the financial institution will "pay" this shortfall from a previously agreed upon additional amount available on the property - understand?

While there is a shortfall between the income generated by the property and the bond payment, insurance, taxes etc., the outstanding capital will obviously increase as each new month arises and each specific shortfall is added onto the outstanding capital (loan at the bank).

Once the shortfall turns into surplusses, the surplusses are in turn paid into the account until the bond is paid in full.

With a normal, say 15 year, bond, the interest payments and principal payments of the constant mortgage payment (assume fixed interest rate) nicely works out to a balance of 0 in month 180, but not in the scenario I describe.

What happens in the scenario I described above is that, if I keep the payment constant (=PMT(and the rest - calculated on the original term and the original investment) each month's deficit is added to the outstanding capital but the payment stays constant.

I want this payment to remain constant throughout the bond.

The outstanding capital gradually increases as deficits are added (the idea is not to have to pay anything out of my pocket into the bond while there is a shortfall).

Then, as the annual rent increases over the next few years, the deficit gets less and less until it turns positive. This positive figure is also added (actually, "deducted") to the outstanding capital and apart from the standard capital portion of the PMT function, lowers the outstanding capital even more.

All of the above causes the bons to be paid in full before the actual initial term is finished.

This is great until I tried getting the bond to actually finish on the date it was supposed to finish, despite all the additions (the deficits) to the outstanding capital and the subtractions (the surplusses) from the same.

I want to keep the payment the same throughout the bond term, assume a constant interest rate is charged and assume a constant net increase in rentals is achieved.

How do I calculate a constant payment for a given term and have it work out to 0 at the end of that term, given the aforementioned standards?

Let's hear your clever ideas, please!

Best regards

Harry
P.S. The attachment is very basic due to size restrictions. Please drag row A24:M24 down up to 240 months in column A (20 year bond)

Morning,

Has anybody ever come up with a way to discover the a compound interest growth rate based on actual numbers... for example;

Jan05 - 75
Jan06 - 150
Jan07 - 290
Jan08 - 440

I have a long list of investments that I am working on for a colleague, and want to show the growth in interest rate. In addition, I have payment schedules from loans for which I want to do the same, although I imagine the formula will be the same.

Any suggestions?

Cheers,

Paul

This may be simple but I'm stumped on how to do this:

My bank has agreed to loan $130,000 if I agree to pay a lump sum of $259,870 in 9 years. What is the annual interest rate.

I've done it on a finanical calculator and got 7.99% but I can't get the formula right on excel, it comes up with #NUM!

Thanks


No luck finding an answer? You could always try Google.