Calcul\desfasurator dobanda

Configurare noua (How To)

Situatie

 Mai jos am creat o procedura ce calculeaza si afiseaza un desfasurator de dobanda bancara.
Procedura are urmatorii parametri:
@SumaLunara – suma lunara pe care ne propunem sa o depunem in fiecare luna in contdepozitplan de acumulare
@NrAni – numarul de ani pentru care dorim sa facem simularea
@ProcentDobanda – procentul de dobanda anuala (de forma 5 daca e 5%, 2.5 daca e 2.5%, etc)
@CapitalizareAnual – se pune 1 daca dobanda se capitalizeaza doar la sfarsitul anului sau se lasa 0 daca dobanda se capitalizeaza lunar
Procedura va afisa urmatoarele coloane:
Anul – anul pt care se face simularea
SoldulInitial – soldul de la inceputul anului
Luna1 – suma depusa + dobanda la sfarsitul primei luni (daca este capitalizare lunara) sau suma depusa (daca este capitalizare anuala). (se pleaca de la premiza ca depunerea de suma se face in prima zi a fiecarei luni)
Luna2  pana la Luna12 – similar cu Luna1
Economie – totalul sumelor depuse pe anul respectiv
Dobanda – dobanda primita in anul respectiv
SoldFinal – soldul la sfarsitul anului respectiv

Solutie

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

— exec [BD_CalculDobanda] 100, 10, 2.45, 0
— =============================================
ALTER PROCEDURE [dbo].[BD_CalculDobanda]

@SumaLunara money,
@NrAni int,
@ProcentDobanda float,
@CapitalizareAnual int
AS
BEGIN

SET NOCOUNT ON;
declare @Anul int
declare @Economie money
declare @Dobanda money
declare @SoldFinal money
declare @SoldInitial money
select @SoldFinal = 0,@SoldInitial =0
create table #tempDobanda
(Anul int, SoldInitial money, Luna1 money,Luna2 money, Luna3 money, Luna4 money, Luna5 money, Luna6 money,
Luna7 money, Luna8 money, Luna9 money, Luna10 money, Luna11 money, Luna12 money,Economie money, Dobanda money, SoldFinal money)

declare @i int
set @i=@NrAni
while(@i>0)
begin
select @Anul=@NrAni-@i+1
if @CapitalizareAnual = 1
begin
select @Economie = 12*@SumaLunara
select @SoldFinal = @SoldFinal+ @Economie
select @Dobanda = @SoldFinal*@ProcentDobanda/100
select @SoldFinal=@SoldFinal + @Dobanda
insert into #tempDobanda(Anul,SoldInitial,Economie,Dobanda,SoldFinal) values (@Anul,@SoldInitial,@Economie,@Dobanda,@SoldFinal)
select @SoldInitial = @SoldFinal
end
— CAPITALIZARE
if @CapitalizareAnual = 0
begin
declare @Luna1 money,@Luna2 money,@Luna3 money,@Luna4 money,@Luna5 money,@Luna6 money,
@Luna7 money,@Luna8 money,@Luna9 money,@Luna10 money,@Luna11 money,@Luna12 money

select @Luna1=@SumaLunara+@SoldInitial+(@SumaLunara+@SoldInitial)*@ProcentDobanda/100 / 12
select @Luna2=(@Luna1+@SumaLunara)*@ProcentDobanda/100 / 12 + @SumaLunara +@Luna1
select @Luna3=(@Luna2+@SumaLunara)*@ProcentDobanda/100 / 12+ @SumaLunara +@Luna2
select @Luna4=(@Luna3+@SumaLunara)*@ProcentDobanda/100 / 12+ @SumaLunara +@Luna3
select @Luna5=(@Luna4+@SumaLunara)*@ProcentDobanda/100 / 12+ @SumaLunara +@Luna4
select @Luna6=(@Luna5+@SumaLunara)*@ProcentDobanda/100 / 12+ @SumaLunara +@Luna5
select @Luna7=(@Luna6+@SumaLunara)*@ProcentDobanda/100 / 12+ @SumaLunara +@Luna6
select @Luna8=(@Luna7+@SumaLunara)*@ProcentDobanda/100 / 12+ @SumaLunara +@Luna7
select @Luna9=(@Luna8+@SumaLunara)*@ProcentDobanda/100 / 12+ @SumaLunara +@Luna8
select @Luna10=(@Luna9+@SumaLunara)*@ProcentDobanda/100 / 12+ @SumaLunara +@Luna9
select @Luna11=(@Luna10+@SumaLunara)*@ProcentDobanda/100 / 12+ @SumaLunara +@Luna10
select @Luna12=(@Luna11+@SumaLunara)*@ProcentDobanda/100 / 12+ @SumaLunara +@Luna11
select @Economie=@SumaLunara*12
select @Dobanda = @Luna12-@Economie*@Anul
select @SoldFinal = @Luna12
insert into #tempDobanda
values (
@Anul,@SoldInitial,
@Luna1,@Luna2,@Luna3,@Luna4,@Luna5,@Luna6,
@Luna7,@Luna8,@Luna9,@Luna10,@Luna11,@Luna12,
@Economie,@Dobanda,@SoldFinal
)
select @SoldInitial=@SoldFinal
end

set @i=@i-1
end
if @CapitalizareAnual = 1
begin
update #tempDobanda set Luna1=@SumaLunara, Luna2=@SumaLunara,Luna3=@SumaLunara,Luna4=@SumaLunara,Luna5=@SumaLunara,
Luna6=@SumaLunara,Luna7=@SumaLunara, Luna8=@SumaLunara,Luna9=@SumaLunara,Luna10=@SumaLunara,Luna11=@SumaLunara,Luna12=@SumaLunara
end
select * from #tempDobanda
END

Tip solutie

Permanent
Etichetare:

Voteaza

(20 din 37 persoane apreciaza acest articol)

Despre Autor

Leave A Comment?