Excel For Finance Tips Calculate Compound Interest, Find Cheaper Loans

I am trying to calculate how to repay interest on several loans that were provided by one person over a 25 year period.

Example: Following are 2 loans showing the dates and amounts of each loan I received over the years. There are several others received thereafter at different dates. I would like to show all transactions on1 worksheet. with repayment amount for each loan plus interest (eg 2.5 compounded quarterly), & breakdown of original amount of loan, amount of accrued interest and total repayment amount. .

1. 8-15-1983 loan amount received=$23,000.00

2. 5-25-1986 loan amount received= $30,000.00

TOTAL: $53,000.00
1.How would I go about setting up a spreadsheet to illustrate above items?

2.What would be a fair way to account for the time value of those loans for each period to the deadline date of September 30, 2007?

Many thanks, Ron

Hi

I want to know the function to calculate the SI and the CI in Excel. I know manually we calculate the Simple Interest = P*R*T and compound Interest = P(1+i)^n . When I calculate the SI using inbuilt function of Excel i.e. PMT as shown in the following link

http://www.ehow.com/how_4813646_calc...est-excel.html

It given right answer. But if on same data i apply P*R*T it gives something different. Also I saw following site

http://www.busysoftorder.com/npv/1.htm

it uses FV function. Kindly advice how to calculate the Si and CI using inbuilt functions. I just want to calculate it when there is no change in the rate of interest in the subsequent years and it will remain same.

Regards

Harbinder Singh

I am trying to set up my excel to calculate daily compound interest.

The amount is 10,000 at 0.75% per day for 6 months.

I have tried several different things with no success - help please

Example, If I bought a property 5 years ago for $134,000 and it's now worth $210,000. What formula will calculate the compound growth rate for the past 5 years?
I can do it on a scientific calculator but can't seem to get excel to do cube roots, 4th roots etc
Thanks for any help provided.
Jeff

Hi there. I'm trying to come up with a way of calculating Money earned over time by compounding interest, but with a twist. After reaching a set amount of money, all money above and beyond does not gain interest. example:
Principal: $120 (user input value)
Duration: 9 (user input value in days, compounding daily)
%Intertest: 4% (user selected value, either 2% or 4%)
Max interest you can earn: $6 (fixed)
Max interest generating money: $150 (variable dependant on %interest, = $150 or $300)
Response/Answer is final value. I don't need the daily results like the example.
Result would be: $170.84
$124.80 (4.80 interest)
$129.79 (4.99)
$134.98 (5.19)
$140.38 (5.40)
$146.00 (5.62)
$151.84 (5.84)
$157.84 (6.00 reached the max interest level)
$163.84 (6.00)
$170.84 (6.00)

my equations I have so far only do one (below 150 total) or the other (above) but not both.
and its just a regular formula: = IF (P<M, IF (P*I^D<M+1,P*I^D,"over limit"),P+6*D)
P=Principal
D=Duration
I=Interest+1 (so 4% becomes 1.04)
M=Max interest making money

Please go easy on me... its been 10 years since I really got into difficult/challenging formulas in excel and vba... I'm pretty rusty, but pick up quick.
I'm thinking this is going to be a custom function, or maybe a module, but not sure.

Thanks!

I've been trying to figure out how to calculate finance charges on auto loans for over a month, and I still have yet to figure out how to make a formula work. Has anyone ever done this before? Auto loans calculate interest on a daily basis, so days to first payment come into account. I cannot seem to get a functioning formula. Any help would be really appreciated, or even a place to start. Here is some data I have to work with to build a formula:

contract date: 05/03/11
first pmt date: 06/17/11
Term: 60 months
Payment: 472.01
Interest Rate: 17.99
Amount Financed: 18453.57

And finally, I know that the finance charge for this loan is $9867.03. What I'm trying to do is calculate participation, which means I need to calculate what the finance charge would be at 15.99%, and so forth. All the information is there, I just can't seem to figure it out and I'm pretty frustrated at it. Now, I know that the actual interest rate is closer to 18.02% from my company loan calculator, but I can't get it perfect.

Hi, I have one large file and I need to compute the final value of investing a particular amount based on the returns given by the stock market. I'm trying to figure out a way that I can compute the answer without going into cell after cell with the same kind of formula. The formula that I'm looking for is like the formula for a compound interest. I.e. Final value = P(1+r)^n. However, in my case, r is always changing and hence P is also changing as well. What I'm currently doing is typing the same kind of formula into each cell. For example to find the year-end value I input: 1000(1.05) then the next year, the year-end value is this: (1000)(1.05)(1.10), and the next may be (1000)(1.05)(0.96)...and so on....does anyone know of a simpler way to do this?

I need to run an interest accrual report on some lines of credit. This is usually easy enough =(interest/365)*(Ending date of the accrual - beginning date of the accrual) * principle balance.

The problem that I am having though is that the lines of credit normally do not run through the entire accrual period. They are either issued in the middle of the period or expire in the middle of the period, and I just need the interest accrual for when they are active.

I was wondering what the easiest way to approach this problem would be.

Does Excel contain a simple interest loan calculation function built in? Or do I need to specify how exactly simple interest calculates on a per diem basis? What I am trying to do is calculate finance charge based on daily interest such as a standard auto loan contract. I am supplying the interest rate, term, and days to first payment - which in itself contains all of the necessary data for calculation (assuming excel has the loan calculation built in), however simple interest is based daily, so the calculation needs to understand days in each month, and totals days across a long period of time, and I seem to remember excel having basic loan calculation features built in, however general excel help only seems to have basic functions. What would be the best way to approach this?

I'm trying to find an elegant way of calculating compound interest on outstanding debts; one big problem is that clients occasionally make ad-hoc payments so I need to maintain a running total, using a changing bank base lending rate. I have taken quite a long look round and it seems this question has not come up before, I hope!